find next used cell

[UALynn]

Awright, I'm just getting frustrated here.

I want to start in cell A1 and working my way down the column locate the next used cell.

So if cells A3, A7, and A98 have data my psuedo code would be like



The data looks like
Col A ColB
Test
data
data

Another
more
more
more

And
more again

sorry the post didn't space things as expected

I know how to use offset.
I have tried using .end(xldown) to get to the next data point in column A, but I keep getting unexpected results.

[Juan Pablo González]

Try using SpecialCells

Dim Rng As Range

Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants)

or more specific

Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants,xlTextValues)

[UALynn]

Thanks, not quite the effect I wanted, but still good to know.

[NateO]

If your data looks like:

ColA ColB
GroupA (Empty)
(Empty) Member1
(Empty) Member2
GroupB (Empty)
(Empty) Member2
(Empty) Member3

Use an autofilter on A and B for non-blanks. Then loop through the visible cells. I.e.,

Set Rng = Range("A:A").SpecialCells(xlvisible)

[UALynn]

I ended up with looping through all the cells in A and B
If A is empty go looking for B and hang on to the last known A value. Do this until B is empty. Then go back and look for the next A and do it all over again.

Works. Ain't pretty, but it works.

[NateO]

I still vote for an autofilter. E.g.,

http://www.mrexcel.com/board2/viewto...=77261&start=8

These are easy, turn on your macro recorder for the syntax.