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

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

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