Macro to delete a row if #REF! appears

[DilbertAsok]

Hi,
I'm fighting with VBA to improve my existing macro.

My current (working) macro - basically copies the top row downwards for 3000 rows, then moves on to the next sheet.

Sub IMPORT()
    Sheets("Sheet1").Select
    Range("A1:I1").Select
    Selection.Copy
    Range("A2:A3000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Sheet2").Select
    Range("A1:I1").Select
    Selection.Copy
    Range("A2:A3000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    etc etc etc
End Sub

Which is fine, unless the top row of "Sheet1" contains an error (which can happen in cell B1 for example).

I want the macro to look at cell B1, if this is an error (will always be of the "#REF!" type), then delete row 1, LOOP back to look at B1, if this is an error, delete, LOOP back, until B1 isn't an error anymore, and then proceed with the macro above.

I've got this with the new code highlighted red, but it's not working...:

Sub IMPORT()
    Sheets("Sheet1").Select
[COLOR=red]    Do
    If IsError("B1") Then
    Selection.Cell("A1").EntireRow.Delete
    End If
    Loop Until IsError("B1") = False
[/COLOR]    Range("A1:I1").Select
    Selection.Copy
    Range("A2:A3000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("Sheet2").Select
    etc etc etc
End Sub

It doesn't seem to delete the rows even the first time, nevermind the Loop back to check again.

Any offers?

Thanks,

 

[DilbertAsok]

And I'm done - thanks to all!

For the benefit of others, my final macro code looks like this:

    Sheets("Sheet1").Select
    Do
    If IsError(Range("B1")) Then Rows(1).Delete
    Loop Until IsError(Range("B1")) = False
    Range("A1:I1").Select
    Selection.Copy
    Range("A2:A3000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("$A:$I").AutoFilter Field:=2, Criteria1:="0.00"
    Range("$A$2:$A$3000").SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ActiveSheet.Range("$A:$I").AutoFilter Field:=2
    Selection.End(xlDown).Select
    Selection.EntireRow.Delete
    Range("A1").Select

    Sheets("Sheet2").Select
 
    etc etc

The penultimate 2 lines (beginning "Selection.End" and "Selection.EntireRow") are because I realised that each sheet would have 1 line of data at the bottom (a total) which shouldn't be included.

The final line (Range("A1").Select) is jsut taking me back to the top because I like to be tidy!

Now just wait for someone to change the goalposts and I'll be back again...