Delete Rows with Empty Cells with empty column 1
 

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

Delete Rows with Empty Cells with empty column 1
 

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

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

Delete Rows with Empty Cells with empty column 1
 

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

Delete Rows with Empty Cells with empty column 1
 

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

Delete Rows with Empty Cells with empty column 1
 

Thanks. That method took care of business.

"Paul B" wrote in message
...
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" wrote in message
...
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