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

"Mike Fogleman" wrote in message
...
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" wrote in message
...
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