This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Excel VBA Macros (52 exercises)
Topic ==>	Loops and conditions (5 exercises)
Level ==>	Average difficulty
Subject ==>	VBA training

Use a Do Until loop to extract invalid data from a list of dating-agency applications in Excel VBA.

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

Before you can do this exercise, you'll need to download and unzip this file .

Open the file called WODA Application.xlsm and save the file with a new name.

Your task is to extract the invalid rows from Sheet2 and move them into a new list on the Errors sheet.

We want to remove any rows which don't have a valid date in column B.

A basic outline for your procedure could be:

Start in cell A2 on Sheet2.
Begin a loop which continues until the active cell is empty.
Check if the value of the cell to the right is a date.
If not, copy the row of data from Sheet2 to the next blank row on the Errors sheet, then delete the row on Sheet2.
If the row does contain a valid date, simply move down to the next row.

You can see some hints to help you below. Stop reading now if you prefer a challenge!

Some hints to help you:

You can use the IsDate function to determine if a value can be interpreted as a date.

If Not IsDate(ValueToTest) Then

You can copy the EntireRow or just the cells that are in the list:

Range(ActiveCell, ActiveCell.End(xlToRight)).Copy

You need to copy each invalid row to the next available blank row on the Errors sheet. You can identify this row in at least two ways:

Each worksheet maintains a separate active cell. If you switch between worksheets, you'll be in the same cell you were the last time you visited that sheet. You can select the top cell in the Errors sheet at the start of the subroutine and then move one cell down each time you copy a row of data to it.
You can use the End and Offset properties. The list on the Errors sheet is empty to begin with so you'll need to go upwards from the bottom of the worksheet then one cell down to reach the next blank cell.

You can delete a row in a worksheet with this code:

ActiveCell.EntireRow.Delete

When you delete a row, by default, all the other cells shift upwards to fill in the gap. This means that you don't need to move down a row if you've just deleted one.

Save and close the file when you have your procedure working.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

You can find other training resources for the subject of this exercise here:

This page has 0 threads

Add a new post