Skip hidden lines?
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 |
Skip hidden lines?
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) On Tue, 16 Dec 2008 11:56:54 -0800, Ben in CA wrote: 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 |
Skip hidden lines?
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 (MVP - Excel) "Ben in CA" wrote in message ... 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 |
Skip hidden lines?
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Ben in CA" wrote in message ... 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 |
Skip hidden lines?
Thanks Rick and Chip!
No offense Chip, but I preferred Rick's final solution. With some modifications, that works great! Ben "Rick Rothstein" wrote: 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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Ben in CA" wrote in message ... 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 |
All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com