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,

 

[VoG]

Perhaps

    Do
        If IsError(Range("B1")) Then Rows(1).Delete
    Loop Until IsError(Range("B1")) = False

 

[Andrew Poulsom]

Try:

    Do
        If IsError(Range("B1").Value) Then
            Range("A1").EntireRow.Delete
        End If
    Loop Until IsError(Range("B1").Value) = False

 

[DilbertAsok]

Re: Macro to delete a row if #REF! appears - sorted. Now to add Autofilter?

Frustratingly simple! Thanks to you both.

Next trick then would be for the macro to filter on column B (for example), show any cells with zero value, and delete those rows. I know the key strokes that I would do to do those quickly myself, but the macro is outfoxing me!

I have...:

    ActiveSheet.Range("$A$1:$I$1000").AutoFilter Field:=2, Criteria1:="0.00"
    Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
    ActiveSheet.Range("$A$1:$I$9").AutoFilter Field:=2
    Range("A1").Select

The problem is the 2nd line - I don't necessarily want to select cell A3, I just want to be in cell A1, and then move down to the next visible cell. A3 may or may not be visible depending on the results of the filter.

 

[Andrew Poulsom]

Re: Macro to delete a row if #REF! appears - sorted. Now to add Autofilter?

Have you tried recording the keystrokes that you know?

 

[DilbertAsok]

Re: Macro to delete a row if #REF! appears - sorted. Now to add Autofilter?

The keystrokes for that bit would just be the down arrow, but that records as selecting a specific cell, rather than moving the current selection down by one cell.

It has to be simple?!

 

[DilbertAsok]

Got it...:

Dim x As Long, y As Long
x = ActiveCell.Row
y = ActiveCell.Column
Do
x = x + 1
Loop Until Cells(x, y).EntireRow.Hidden = False
Cells(x, y).Select

Which works on a filtered list. But can someone explain the "Dim" commands etc so I understand them, rather than just "this works"?

Thx

 

[Andrew Poulsom]

Have you tried recording Edit|Go To|Special|Visible Cells only and deleting the visible cells (after AutoFiltering of course)?

 

[DilbertAsok]

Interesting - didn't know you could do that, but it looks like that selects the whole sheet, where as I would want to leave row 1 as is and select everything below that?

Edit - this is killing me.

If I use the code:

Dim x As Long, y As Long
x = ActiveCell.Row
y = ActiveCell.Column
Do
x = x + 1
Loop Until Cells(x, y).EntireRow.Hidden = False
Cells(x, y).Select


It works for the first time but I have this many times in my Macro (once for each of 20 sheets). I was hoping that the "Dim x As Long" line would reset the values for x & y.../

 

[Andrew Poulsom]

Try this:

Range("$A$2:$I$1000").SpecialCells(xlCellTypeVisible).EntireRow.Delete