Exercise: Extract Invalid Data from a List of Dating-Agency Applicants

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.

The answer to the exercise will be included and explained if you attend the course listed below!

Category ==> Excel VBA Macros  (49 exercises)
Topic ==> Loops and conditions  (5 exercises)
Level ==> Average difficulty
Course ==> Excel VBA macros
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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.

Original data

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:


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.

This page has 0 threads Add post