Finding the first empty cell in a column?

[Ste_Moore01]

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
	A	B	C	D	E	F	G
1	97325	1	1		97325	1	1
2	97325	1			97326	2	2
3	97325	1			97327	3	3
4	97325	1			97328	4	4
5	97325	1				5	5
6	97325	1			97325	1
7	97325	1			97325	1
8	97325	1			97325	1
9	97325	1			97325	1
10	97325	1			97325	1
11	97325	1			97325	1
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!

 

[Tom Urtis]

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]

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]

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.