How to get select range that excludes hidden content?

[Carson Dyle]

A while back I got some great help here with how to work with a selected range. Pretty straightforward.

Please Login or Register  to view this content.

I'd like to do the same thing now, except exclude any hidden rows and columns. I also want to make sure that I can still loop over that range using simple for loops.

Please Login or Register  to view this content.

I'm guessing that each cell may also have a hidden/visible property that I could examine (haven't looked into it yet), but if I could just get a range that excludes those cells it would be much easier.

[PeteABC123]

Highlight your range. F5 (go to) Special. Visible Cells Only. Click OK.

Pete

[Carson Dyle]

I'm sure that would work, but I'd prefer to keep the simple method of selecting the range that I've been using and handle it in code instead. If it's doable.

[protonLeah]

set r = selection.specialcells(xlvisible)

[PeteABC123]

See protonLeah reply.

[Carson Dyle]

set r = selection.specialcells(xlvisible)

Thanks. Here's a problem I'm running into with that. The row and column counts for the selected range stop at the first hidden row or column.

I'll see if I can explain. I select this range, with column D and rows 3 to 6 hidden. When I get the row and column counts, I get

r.Rows.Count -> 2
r.Colums.Count -> 3

	A	B	C	E
1	Joe Smith	Mar 8, 1969	New York	True
2	Bob Jones	Nov 17, 1984	California	False
7	Ed Connors	Apr 14, 1983	Florida	True
8	Lisa Schrum	May 9, 1959	New York	False
9	David Decker	Jul 9, 1981	Minnesota	True

[Carson Dyle]

No ideas how I can loop over the cells within that range? Being able to select the range doesn't do me much good unless I can also work with the data.

[protonLeah]

Please Login or Register  to view this content.