Delete Rows with Empty Cells with empty column 1
I get an error "Cannot use that command on overlapping selections". It
doesn't like " rng.EntireRow.Delete"
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sub DeleteRowsWithEmptyColumn1()
' deletes rows with empty cells in column 1
Dim rng As Range
On Error Resume Next
Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) _
.SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub
Gord Dibben MS Excel MVP
On Fri, 29 Sep 2006 19:39:09 -0500, "Scott"
wrote:
My below sub below corectly deletes any rows that have empty cells within
a
hard-coded range of A1:A150. What I'd like to do is to search the entire
range of cells containing values and delete any rows that have empty
column
1 cells.
It would achieve the same reults as my current code, but I could get rid
of
my hard coded reference. I never will know how many rows my spreadsheet
will
be and would like to avoid hard-code reference.
Can someone help me re-write my code to achieve this?
CODE **********************
Sub DeleteRowsWithEmptyColumn1()
' deletes rows with empty cells in column 1
Dim rng As Range
On Error Resume Next
Set rng = ActiveSheet.Range("A1:A150").SpecialCells(xlBlanks )
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub
|