Finding the first empty cell in a column?

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
Hi guys and gals,

How do I find the first empty cell in a column using vba and then delete every row after that?

For example
Print Del Notes for Invoiced Goods.xls
ABCDEFG
197325119732511
2973251 9732622
3973251 9732733
4973251 9732844
5973251 55
6973251 973251
7973251 973251
8973251 973251
9973251 973251
10973251 973251
11973251 973251
WORKOUT


I would like to find the first blank cell in column G and delete that row and every other row below it.

So rows 6 to 65536 would need to be deleted (or cleared).

Any help would be greatly appreciated.

Thanks! :LOL:
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
Sub Test1()
Dim x&, z&
If IsEmpty(Range("G1")) = False Then
x = 1
ElseIf IsEmpty(Range("G2")) = False Then
x = 2
Else
x = Range("G1").End(xlDown).Row
End If
Rows(x + 1 & ":" & Cells.Rows.Count).Delete
End Sub
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
Thanks for the quick reply but (using the example I posted above) it deletes everything from row 2 onwards.

I'm not sure why..

The reason I'm asking how to do this is because I've copied the data from columns A to C and only pasted the values (PasteSpecial) and if I use the xlUp and xlDown codes they treat the pasted cells as though there is something in them when there is not.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,225
When I wrote and tested the code I used column G as the criteria column because that was the one you used as an example in your post. If need be, just modify the code to reflect the column that the last row depends on. Also, we are using IsEmpty, which is not necessarily the same as Blank. So, if you have formulas, especially formulas that return null strings and make the cell *look* blank in column G or whatever column you use, really, the code will know something is in those cells, hence not evaluate them as empty, and perhaps give the illusion nothing is happening because the row below last row of formulas is being deleted through 65536, not easy to recgnize that.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,242
Messages
5,600,506
Members
414,385
Latest member
Lioness227

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
Top