Finding next unhidden row

[Cory Layman]

I am trying to find an efficient way to find the next unhidden row in
a range of cells. Normally this isn't a big issue, but when working
with a sheet with 100,000 rows, it can get really slow.

Here is some sample code of how I'm currently doing it.

DIM SourceCell, TargetCell as RANGE
Set SourceCell = Range("A1")

For Each TargetCell in Range("C1","C10000")
If TargetCell.RowHeight > 0 then TargetCell.Value =
SourceCell.Value
Next TargetCell

Rather then looping through each cell in the range and checking the
height, is there a more efficient way to do it?

 

[Per Jessen]

Hi

I think this is what you want:


Sub test()
Dim SourceCell As Range, TargetRange As Range
Set SourceCell = Range("A1")
Set TargetRange = Range("C1", "C10000")

Set TargetRange = TargetRange.SpecialCells(xlCellTypeVisible)
TargetRange.Value = SourceCell.Value
End Sub

Regards,
Per