DELETE ROWS BASED ON BLANK CELLS IN COL "A"

Eddie G.

Board Regular
Joined
Feb 27, 2002
Messages
98
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....
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
This is fairly quick:

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

Hope this helps.
 
Upvote 0
Nate, thanks, that is exactly what i am looking for.........thanks to all else who responded.......is this a great board or what???
 
Upvote 0
You're welcome Eddie, have a good one. I concur, this is good stuff! :)

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-04-25 18:48
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top