Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Is the cell hidden?
I've Subtotalled a worksheet which resulted in rows being hidden. I now want
to loop thru column A of the worksheet and "massage" the data in only the visible rows. How can I do this? The only way I could think of was: Columns("A:A").Select Selection.SpecialCells(xlCellTypeVisible).Select And then cycle the selection.... -- | +-- Thief_ | |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Is the cell hidden?
Hi,
You might be able to use something like this to get to the lines you wnat to "massage": Cells.Find(What:="* Total", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Thanks, "Thief_" wrote: I've Subtotalled a worksheet which resulted in rows being hidden. I now want to loop thru column A of the worksheet and "massage" the data in only the visible rows. How can I do this? The only way I could think of was: Columns("A:A").Select Selection.SpecialCells(xlCellTypeVisible).Select And then cycle the selection.... -- | +-- Thief_ | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Is the cell hidden?
Hello Again,
That's the right approach, however it uses all of the visible cells in column A. You need to find just the visible cells within the subtotaled area... '----------------------------- Sub TestNewsPost() Dim rngCell As Excel.Range Dim rngVisible As Excel.Range Set rngVisible = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp)) Set rngVisible = rngVisible.SpecialCells(xlCellTypeVisible) For Each rngCell In rngVisible 'Do something Next 'rngCell Set rngCell = Nothing Set rngVisible = Nothing End Sub '----------------------------- Jim Cone San Francisco, USA "Thief_" wrote in message ... I've Subtotalled a worksheet which resulted in rows being hidden. I now want to loop thru column A of the worksheet and "massage" the data in only the visible rows. How can I do this? The only way I could think of was: Columns("A:A").Select Selection.SpecialCells(xlCellTypeVisible).Select And then cycle the selection.... -- | +-- Thief_ | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Is the cell hidden?
Thief,
If the action you are performing is the same for each visible cell, I believe this will work: Range("A1:A2000").SpecialCells(xlCellTypeVisible). Font.Bold = True If you need to loop through like you said, then I think this is the best way. Notice that I turned off screenupdating and limited the rows. If you select the whole column ("A:A") it's pretty slow and it converts your used range to 65536 rows, which I don't think you want. Sub test() Dim visible_cell As Range On Error GoTo err_handler Application.ScreenUpdating = False For Each visible_cell In Range("A1:A2000").SpecialCells(xlCellTypeVisible) visible_cell.Font.Bold = True Next visible_cell err_handler: Application.ScreenUpdating = True End Sub hth, Doug "Thief_" wrote in message ... I've Subtotalled a worksheet which resulted in rows being hidden. I now want to loop thru column A of the worksheet and "massage" the data in only the visible rows. How can I do this? The only way I could think of was: Columns("A:A").Select Selection.SpecialCells(xlCellTypeVisible).Select And then cycle the selection.... -- | +-- Thief_ | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Is the cell hidden?
Hi,
This looks good. I had assumed you had collapsed it and that the SubTotals were the only things visible. Never make an assume anything, right. What Doug haas here looks good, except if you are going to do something in the Worksheet, you might want to put a simple Stop in it for each occurance and I think I would take out the Application.ScreenUpdating = False, so you can keep track of what you have already "touched." Thanks, "Doug Glancy" wrote: Thief, If the action you are performing is the same for each visible cell, I believe this will work: Range("A1:A2000").SpecialCells(xlCellTypeVisible). Font.Bold = True If you need to loop through like you said, then I think this is the best way. Notice that I turned off screenupdating and limited the rows. If you select the whole column ("A:A") it's pretty slow and it converts your used range to 65536 rows, which I don't think you want. Sub test() Dim visible_cell As Range On Error GoTo err_handler Application.ScreenUpdating = False For Each visible_cell In Range("A1:A2000").SpecialCells(xlCellTypeVisible) visible_cell.Font.Bold = True Next visible_cell err_handler: Application.ScreenUpdating = True End Sub hth, Doug "Thief_" wrote in message ... I've Subtotalled a worksheet which resulted in rows being hidden. I now want to loop thru column A of the worksheet and "massage" the data in only the visible rows. How can I do this? The only way I could think of was: Columns("A:A").Select Selection.SpecialCells(xlCellTypeVisible).Select And then cycle the selection.... -- | +-- Thief_ | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA- Is the cell hidden?
David & friends,
Thanks for your help. David, you are correct in your assumption. My subtotals are collapsed to the second level, of three available, and only the Totals rows are visible. -- | +-- Thief_ | "David" wrote in message ... Hi, This looks good. I had assumed you had collapsed it and that the SubTotals were the only things visible. Never make an assume anything, right. What Doug haas here looks good, except if you are going to do something in the Worksheet, you might want to put a simple Stop in it for each occurance and I think I would take out the Application.ScreenUpdating = False, so you can keep track of what you have already "touched." Thanks, "Doug Glancy" wrote: Thief, If the action you are performing is the same for each visible cell, I believe this will work: Range("A1:A2000").SpecialCells(xlCellTypeVisible). Font.Bold = True If you need to loop through like you said, then I think this is the best way. Notice that I turned off screenupdating and limited the rows. If you select the whole column ("A:A") it's pretty slow and it converts your used range to 65536 rows, which I don't think you want. Sub test() Dim visible_cell As Range On Error GoTo err_handler Application.ScreenUpdating = False For Each visible_cell In Range("A1:A2000").SpecialCells(xlCellTypeVisible) visible_cell.Font.Bold = True Next visible_cell err_handler: Application.ScreenUpdating = True End Sub hth, Doug "Thief_" wrote in message ... I've Subtotalled a worksheet which resulted in rows being hidden. I now want to loop thru column A of the worksheet and "massage" the data in only the visible rows. How can I do this? The only way I could think of was: Columns("A:A").Select Selection.SpecialCells(xlCellTypeVisible).Select And then cycle the selection.... -- | +-- Thief_ | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
write in a cell and the cell above is temporarly hidden | Excel Worksheet Functions | |||
2007 Worksheet, Hidden Columns, .CSV Format Saves Hidden Column Da | Excel Discussion (Misc queries) | |||
How do I detect hidden worksheets or hidden data on a worksheet? | Excel Discussion (Misc queries) | |||
I need my Hidden Rows to stay hidden when I print the sheet. | Excel Discussion (Misc queries) | |||
Saving hidden data with a worksheet (preferably without using a hidden sheet) | Excel Programming |