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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,222
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,222
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,118,736
Messages
5,573,921
Members
412,555
Latest member
mark84
Top