DELETE ROWS BASED ON BLANK CELLS IN COL "A"

[Eddie G.]

I am trying to use some existing code to do this but I am having no luck. I need a macro that will search all of column A and delete rows if the cells in COL A are blank. thanks....

[Ricky Morris]

Here's a non VBA solution:

1. Add another column with sequential numbers (values) to your worksheet.
2. Sort the worksheet by column A. This will place all the rows with blanks in column A together.
3. Highlight and delete the rows with blanks in column A.
4. Re-sort the worksheet based on the column you added in step 1.
5. Delete the column you added in step 1.

_________________
It's never too late to learn something new.

Ricky

[ This Message was edited by: Ricky Morris on 2002-04-25 09:06 ]

[Joe Was]

Try this. JSW

Sub DRow()
'Find all the rows for which column "A" = "" and delete it.

Application.ScreenUpdating = False
Worksheets("Sheet1").Select
Range("A1").Select
For Each r In Worksheets("Sheet1").UsedRange.Rows
n = r.Row
If Worksheets("Sheet1").Cells(n, 1) = "" Then
Worksheets("Sheet1").Cells(n, 1).Select
Selection.EntireRow.Delete
End If
Next r
Application.ScreenUpdating = True
'Range("A1").Select
End Sub


[ This Message was edited by: Joe Was on 2002-04-25 10:10 ]

[Eddie G.]

Hi Joe,

Your code stops after the first few rows are deleted. any suggestions?

[sen_edp]

Hello Eddie

download the very useful addin asap utilities
from

http://www.asap-utilities.com

it is free and works great for excel stuff like that



Andreas

[Joe Was]

The code looks for an empty cell in column A. If you have a blank in a cell in column A the code will skip that row.

Move your cursor to one of the skiped rows and delete the contents of a missed cell in column A, then re-run the the code. I would mark the cell to the right to test. If the code removes your cleaned up cell row then thats the problem. The test cells are not truly empty?

Modify the code with a " " inplace of the "". Then add a copy of the code below the first macro but inside the Sub. That should fix it? JSW

[invisigirl]

Why not just autofilter column A, display the blanks & then delete those rows? I know that's pretty simplistic, but it works for me.

And the ASAP utilities are well worth downloading. A lot of time-saving features in there!

[NateO]

This is fairly quick:

Sub deltime2()
[a:a].SpecialCells(xlCellTypeBlanks).EntireRow.delete
End Sub

Hope this helps.

[Eddie G.]

Nate, thanks, that is exactly what i am looking for.........thanks to all else who responded.......is this a great board or what???

[NateO]

You're welcome Eddie, have a good one. I concur, this is good stuff!

_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-04-25 18:48 ]