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_
|

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_
|

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_
|

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_
|

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_
|

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_
|