Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skip hidden rows while copying down in excel? | Excel Discussion (Misc queries) | |||
How do I copy information and skip hidden cells? | Excel Discussion (Misc queries) | |||
skip hidden sheets | Excel Programming | |||
How do I copy cells and skip lines? | Excel Discussion (Misc queries) | |||
Excel skip some lines of VBA code | Excel Programming |