Find/Delete Row and Row Above

Hi ed

Try this one.

Sub DeleterefRows()
'Written by OzGrid Business Applications
'www.ozgrid.com

Dim rRows As Range
Dim rCells As Integer
Dim i As Integer

Application.ScreenUpdating = False
Set rRows = Selection.EntireColumn

For i = 1 To WorksheetFunction.CountIf(rRows, "#REF!")
rRows.Find(What:="#REF!").Offset _
(-1, 0).Range("A1:A2").EntireRow.Delete
Next i

Set rRows = Nothing
Application.ScreenUpdating = True
End Sub

Dave
OzGrid Business Applications

Forgot to say...

Before running it, just select any cell in the Column you want checked.

Dave

OzGrid Business Applications

Hi ed Sub DeleterefRows() 'Written by OzGrid Business Applications 'www.ozgrid.com Dim rRows As Range Dim rCells As Integer Dim i As Integer Application.ScreenUpdating = False Set rRows = Selection.EntireColumn For i = 1 To WorksheetFunction.CountIf(rRows, "#REF!") rRows.Find(What:="#REF!").Offset _ (-1, 0).Range("A1:A2").EntireRow.Delete Next i Set rRows = Nothing Application.ScreenUpdating = True End Sub

Dave

Dave,

This worked great! As always, Thanks for the help! By the way, I might hit you up for some training in the near future.

Thanks again,

Ed

Hi ed Sub DeleterefRows() 'Written by OzGrid Business Applications 'www.ozgrid.com Dim rRows As Range Dim rCells As Integer Dim i As Integer Application.ScreenUpdating = False Set rRows = Selection.EntireColumn For i = 1 To WorksheetFunction.CountIf(rRows, "#REF!") rRows.Find(What:="#REF!").Offset _ (-1, 0).Range("A1:A2").EntireRow.Delete Next i Set rRows = Nothing Application.ScreenUpdating = True End Sub

Dave

Dave,

This worked great! As always, Thanks for the help! By the way, I might hit you up for some training in the near future.

Thanks again,

Ed

Ed, LOL! Yeah I do sleep, but I quite often work nights. I guess that is one of the drawbacks living in Oz and running an E-Business.

Look forward to hearing from you.

DaveOzGrid Business Applications