VBA that finds the last cell with a value in it

Set top =Range("G" & rows.count).end(xlup).row

Set top = Cells(Rows.Count, "G").End(xlUp)

Do you want start counting after row 4?

try this.....


Range("G1").Select

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Offset(-1, 0).Select

Range("G1").Select

Do Until ActiveCell = ""
ActiveCell.Offset(1, 0).Select

Loop

ActiveCell.Offset(-1, 0).Select

MsgBox ActiveCell.Address

rinkydink,

A gentle reminder to please read earlier - and perhaps test - Expert comments before posting

Futher posts should only be made where the proposed solution is demonstrably better than earlier comments, or if the Author  has clarified their request further.

In this case while your solution will work, it will be much slower than the earlier comments provided by two highly ranked Experts

Regards

Dave

Hi Dave, I have a problem here. I trust your judgment implicitly but the only one that I can get to work is rinkydink's. Please take a look at ippinto's and rory's suggestions  and let me know if they work for you. If they do please take a look at my demo workbook let me know what I'm doing wrong.

Thanks,
John

John,

Rinkydink's code answered a slightly different interpretation of last used cell - which as it turns out is the one you are seeking

Rory and jppinto have provided code that will find the last used value (looking from the bottom up)

Rinkydink's code finds the last cell that procedes the first cell with a "" value  (nc which can be blank or "" as per your formula) top down

I had assumed that both codes would find the same answer, but as you have data .. spaces .. data then a different outcome results.. So in this case rinkydink does indeed deserve  the credit

fwiw, you can get the same result quickly with a Find (no for points for me pls)

Regards
Dave

Sub AltCode()
Range([g4], Cells(Rows.Count, "G")).Find(vbNullString, [g4], xlValues, , , xlNext).Offset(-1, 0).Activate
End Sub

                                              

1:
2:
3:

rinkydink , you got there first with an answer that worked, but Patrick's does work faster, hence the split.

Thanks to both of you.

- John

Dave, your code selects the cell nicely, but ultimately I want to use 'top' as the top of a Range(top, btm) thing, and I couldn't figure out how to adapt the logic of yours to that. But thanks.

In retrospect I should have phrased the question like this:

"How do I write this line so that it identifies the last cell in Column G that has a value, as opposed to the last cell containing a formula?"