Delete Rows with Empty Cells with empty column 1

  • Thread starter Thread starter Scott
  • Start date Start date
S

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
 
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
 
I get an error "Cannot use that command on overlapping selections". It
doesn't like " rng.EntireRow.Delete"
 
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
 
for some reason, the code still isn't deleting my row with empty cell in
column 1. Can you check it again?
 
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
 
Thanks. That method took care of business.

Paul B said:
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
Click to expand...
 
Back
Top