Delete Rows with Empty Cells with empty column 1

[Scott]

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

 

[Gord Dibben]

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

 

[Scott]

I get an error "Cannot use that command on overlapping selections". It
doesn't like " rng.EntireRow.Delete"

 

[Mike Fogleman]

You need to find the last row as a variable:

Sub DeleteRowsWithEmptyColumn1()
' deletes rows with empty cells in column 1
Dim rng As Range
Dim LRow As Long

On Error Resume Next
LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ActiveSheet.Range("A1:A" & LRow).SpecialCells(xlBlanks)
On Error GoTo 0
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub

Mike F

 

[Scott]

for some reason, the code still isn't deleting my row with empty cell in
column 1. Can you check it again?

 

[Paul B]

Scott, the code from Gord and Mike work for me, here is another way

Sub Delete_blank_Column1()
On Error Resume Next
Intersect(Range("A:A"), ActiveSheet.UsedRange) _
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Scott]

Thanks. That method took care of business.

Scott, the code from Gord and Mike work for me, here is another way

Sub Delete_blank_Column1()
On Error Resume Next
Intersect(Range("A:A"), ActiveSheet.UsedRange) _
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003