Delete entire rows where there is a blank in column A

Hello,

Could someone please advise on a macro code that would do the following:

If there is a blank cell in column A, then delete the entire row . I
need this to loop around from A2:A60000.

Any help would be greatly appreciated.

Thanks,

Longbow :-)

Live Long and Prosper

*** Sent via Developersdex http://www.developersdex.com ***

The easiest might be to sort and then delete

or if you don't want to sort
Sub deleteblanks()
Columns(1).SpecialCells(xlBlanks).Delete
End Sub

--
Don Guillett
SalesAid Software
[email protected]
"Chris Hankin" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Could someone please advise on a macro code that would do the following:
>
> If there is a blank cell in column A, then delete the entire row . I
> need this to loop around from A2:A60000.
>
> Any help would be greatly appreciated.
>
> Thanks,
>
> Longbow :-)
>
> Live Long and Prosper
>
> *** Sent via Developersdex http://www.developersdex.com ***

Hi Chris

Try this
http://www.rondebruin.nl/specialcells.htm


--
Regards Ron De Bruin
http://www.rondebruin.nl



"Chris Hankin" <[email protected]> wrote in message news:[email protected]...
> Hello,
>
> Could someone please advise on a macro code that would do the following:
>
> If there is a blank cell in column A, then delete the entire row . I
> need this to loop around from A2:A60000.
>
> Any help would be greatly appreciated.
>
> Thanks,
>
> Longbow :-)
>
> Live Long and Prosper
>
> *** Sent via Developersdex http://www.developersdex.com ***

One way

Sub Macro1()
'
Dim cell As Range

For Each cell In Range("A2":A6000")
If cell.Value = "" Then
cell.EntireRow.Delete
End If
Next cell
End Sub

"Chris Hankin" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Could someone please advise on a macro code that would do the following:
>
> If there is a blank cell in column A, then delete the entire row . I
> need this to loop around from A2:A60000.
>
> Any help would be greatly appreciated.
>
> Thanks,
>
> Longbow :-)
>
> Live Long and Prosper
>
> *** Sent via Developersdex http://www.developersdex.com ***

Thanks to Alf Bryn, Don Guillett and Ron de Bruin for your help - very
much appreciated. Unfortunately, these macro codes worked but did not
delete all the rows that had blanks in column A.

Alf's code worked the best but I had the following problem:

At the moment I have 6 rows of data that I wish to keep and each of
these rows contains data in column A. However, in column S and Y, I
have approximately 60,000 rows of data that has no data in their
corresponding column A. As this is a dynamic spreadsheet (it gets
larger over time) I need to delete all rows that contain data in columns
S and Y but have no data in column A.

With Alf's code, what happened was that when the code applied to row 7
(first blank row in column A) it deleted OK. However, after it deleted
this row, the row became "A7" again and again... and consequently the
code did not continue deleting as it contined its loop to row 8, 9, 10
...

If anyone can please help that would be great.

Kind regards,

Longbow :-)

Live Long and Prosper

*** Sent via Developersdex http://www.developersdex.com ***

hi Chris

Alf's code is wrong because it not delete from the bottom up
Maybe you cells are not really blank ??

See this page for looping examples
http://www.rondebruin.nl/delete.htm



--
Regards Ron De Bruin
http://www.rondebruin.nl



"Chris Hankin" <[email protected]> wrote in message news:[email protected]...
> Thanks to Alf Bryn, Don Guillett and Ron de Bruin for your help - very
> much appreciated. Unfortunately, these macro codes worked but did not
> delete all the rows that had blanks in column A.
>
> Alf's code worked the best but I had the following problem:
>
> At the moment I have 6 rows of data that I wish to keep and each of
> these rows contains data in column A. However, in column S and Y, I
> have approximately 60,000 rows of data that has no data in their
> corresponding column A. As this is a dynamic spreadsheet (it gets
> larger over time) I need to delete all rows that contain data in columns
> S and Y but have no data in column A.
>
> With Alf's code, what happened was that when the code applied to row 7
> (first blank row in column A) it deleted OK. However, after it deleted
> this row, the row became "A7" again and again... and consequently the
> code did not continue deleting as it contined its loop to row 8, 9, 10
> ..
>
> If anyone can please help that would be great.
>
> Kind regards,
>
> Longbow :-)
>
> Live Long and Prosper
>
> *** Sent via Developersdex http://www.developersdex.com ***

Thanks Ron for the information. I visited the site you recommended and
used the following macro code which worked very well:

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.Count


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

Kind regards,

Chris.

Live Long and Prosper

*** Sent via Developersdex http://www.developersdex.com ***

I posted that macro in my first reply to you ?

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Chris Hankin" <[email protected]> wrote in message news:[email protected]...
> Thanks Ron for the information. I visited the site you recommended and
> used the following macro code which worked very well:
>
> 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.Count
>
>
> 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
>
> Kind regards,
>
> Chris.
>
> Live Long and Prosper
>
> *** Sent via Developersdex http://www.developersdex.com ***