How to remove rows with the first cell blank
 

Try this for column A

Sub DeleteBlankRows_2()
'This macro delete all rows with a blank cell in column A
'If there are no blanks or there are too many areas you see a MsgBox
Dim CCount As Long
On Error Resume Next

With Columns("A") ' You can also use a range like this
Range("A1:A8000")

CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Cou nt

If CCount = 0 Then
MsgBox "There are no blank cells"
ElseIf CCount = .Cells.Count Then
MsgBox "There are more then 8192 areas"
Else
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If

End With

On Error GoTo 0
End Sub


--



Regards Ron de Bruin
http://www.rondebruin.nl



"Grd" wrote in message
...
Hi ,

I have a block of data from a database export. I need to get rid of rows
which have the first cell blank.

I need to automate this. At the moment I do it manually.

Can this be done in excel vBA. Any help greatly appreciated.

Suzanne

How to remove rows with the first cell blank
 

This works BEAUTIFULLY!!

"Ron de Bruin" wrote:

Try this for column A

Sub DeleteBlankRows_2()
'This macro delete all rows with a blank cell in column A
'If there are no blanks or there are too many areas you see a MsgBox
Dim CCount As Long
On Error Resume Next

With Columns("A") ' You can also use a range like this
Range("A1:A8000")

CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Cou nt

If CCount = 0 Then
MsgBox "There are no blank cells"
ElseIf CCount = .Cells.Count Then
MsgBox "There are more then 8192 areas"
Else
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If

End With

On Error GoTo 0
End Sub


--



Regards Ron de Bruin
http://www.rondebruin.nl



"Grd" wrote in message
...
Hi ,

I have a block of data from a database export. I need to get rid of rows
which have the first cell blank.

I need to automate this. At the moment I do it manually.

Can this be done in excel vBA. Any help greatly appreciated.

Suzanne