557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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:
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.
25 Aytoun Street