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 ***

Delete entire rows where there is a blank in column A
 

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

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

Delete entire rows where there is a blank in column A
 

Hi Chris

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


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



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

Delete entire rows where there is a blank in column A
 

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

Delete entire rows where there is a blank in column A
 

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 ***

Delete entire rows where there is a blank in column A
 

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

Delete entire rows where there is a blank in column A
 

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

Kind regards,

Chris.

Live Long and Prosper :)

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

Delete entire rows where there is a blank in column A
 

I posted that macro in my first reply to you ?

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



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

Kind regards,

Chris.

Live Long and Prosper :)

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