Next Non-blank cell in a Range

Question

I am trying to get the next non blank cell of a column.

I would like to find a way to do it with something already existing (without a custom loop).

For example, I searched around the Find function :

item = Worksheets(ws).Columns(1).Find(what:= something , after:= previousitem)

But here I would need a what:= parameters, which mean non blank

Does this answer your question? [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) — BigBen, Dec 08 '19 at 01:38
What:="*" works quite fine until the last one. But it return the first row of the column at the end — Foxhunt, Dec 08 '19 at 11:05
You just need to check the returned address to know when you're done. — Ron Rosenfeld, Dec 08 '19 at 12:10
Yes that's what i'll do. But i was wondering why i did not get "nothing" — Foxhunt, Dec 08 '19 at 12:42

score 1 · Answer 1 · answered Dec 08 '19 at 13:30

1

This can be done, using following key combination:

Ctrl + Arrow-Down

Record this in a macro and Excel will generate the correct VBA code for it.

answered Dec 08 '19 at 13:30

Dominique

score 1 · Accepted Answer · answered Dec 08 '19 at 13:30

1

So this works :

item = Worksheets(ws).Columns(1).Find(what:= "*" , after:= previousitem)

answered Dec 08 '19 at 13:30

Foxhunt

But the Find does not return nothing on the last item, but the first item of the list – Foxhunt Dec 08 '19 at 17:19

2 Answers2