Skip hidden lines?

[Ben in CA]

Hi,

Thanks to some super helpful people in this community, I've been able to do
some pretty cool stuff.

However, now I've had to modify the spreadsheet such that it has some hidden
rows in the midst of where this script places it's values. Is there a way
that it can "skip" hidden rows? (For instance, if rows 8 and 9 are hidden,
that it skips them, and copies values subsequent to 7 into the next visible
row - 10.)

Thanks in advance,

Ben


Private Sub SuperSum()
TargetCell = "U6" ' First cell to start coping the selected numbers to
Range(TargetCell, Range(TargetCell).End(xlDown)).ClearContents
For Each cell In Selection
Range(TargetCell).Offset(off, 0) = cell.Value ' Can hidden rows be
excluded?
MyResult = MyResult + cell.Value
off = off + 1
Next
Range("U1").Select ' Keep from overwriting the other fields
Range(TargetCell).Offset(off, 0) = MyResult
Selection = Range(TargetCell).Offset(off, 0)

Application.GoTo Reference:="R65536C21" ' The range of cells examined for
selections
Selection.End(xlUp).Select

Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 10
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThick
End With

Range("U1").Select
Selection.Clear
Range("U200").Select
Selection.End(xlUp).Select

End Sub

 

[Chip Pearson]

Try something like changing

For Each cell In Selection
Range(TargetCell).Offset(off, 0) = cell.Value ' Can hidden rows be
excluded?
MyResult = MyResult + cell.Value
off = off + 1
Next

to
For Each cell In Selection
If cell.EntireRow.Hidden = False Then
MyResult = MyResult + cell.Value
End If
Next cell

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Rick Rothstein]

Add this code immediately after the "For Each cell In Selection"
statement...

Do While Range(TargetCell).Offset(off, 0).EntireRow.Hidden
off = off + 1
Loop

 

[Rick Rothstein]

Actually, the solution I proposed doesn't work if the total cell would end
up at a hidden row. This code handles that problem as well as your original
problem...

Sub SuperSum()
Dim Off As Long
Dim C As Range
Dim Target As Range
Dim Total As Double
Set Target = Range("U6")
Range(Target, Cells(Rows.Count, "U").End(xlUp)).Clear
For Each C In Selection
Do While Target.EntireRow.Hidden
Set Target = Target.Offset(1)
Loop
Target.Value = C.Value
Total = Total + C.Value
Set Target = Target.Offset(1)
Next
Do While Target.EntireRow.Hidden
Set Target = Target.Offset(1)
Loop
Target.Value = Total
With Target.Borders(xlEdgeTop)
.ColorIndex = 0
.Weight = xlThick
End With
End Sub

 

[Ben in CA]

Thanks Rick and Chip!

No offense Chip, but I preferred Rick's final solution.

With some modifications, that works great!

Ben