Is it possible to tell if a cell is hidden by 'Autofilter'
 

? cells(18,4).EntireRow.Hidden
False
Rows(18).Hidden = True
? cells(18,4).EntireRow.Hidden
True


--
Regards,
Tom Ogilvy


TonyJeffs wrote in message
om...
Suppose I use the autofilter command so that only rows 10 16 18 22 are
visible,
Is it possible to establish whether a cell is visible using a macro?
Something like:
msgbox cells(18,4).visible

Thanks
Tony

Is it possible to tell if a cell is hidden by 'Autofilter'
 

you might want to think a bit differently

dim rng as Range
dim rng1 as Range
set rng = Activesheet.Autofilter.Range.Columns(1).cells
' adjust range not to include header row
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
' adjust range to refer only to visible cells
On Error Resume Next
set rng1 = rng.SpecialCells(xlVisible)
On Error goto 0
if not rng1 is nothing then
for each cell in rng1
sStr = sStr & cell.Address & ", "
Next
sStr = left(sStr,len(sStr)-2)
msgbox sStr
Else
msgbox "No visible rows"
End if

This will allow you to work with the visible cells in the filter


Tom Ogilvy wrote in message
...
? cells(18,4).EntireRow.Hidden
False
Rows(18).Hidden = True
? cells(18,4).EntireRow.Hidden
True


--
Regards,
Tom Ogilvy


TonyJeffs wrote in message
om...
Suppose I use the autofilter command so that only rows 10 16 18 22 are
visible,
Is it possible to establish whether a cell is visible using a macro?
Something like:
msgbox cells(18,4).visible

Thanks
Tony

Is it possible to tell if a cell is hidden by 'Autofilter'
 

Just something for discussion...When you use the term "Hidden", and
"RowHeight," just be aware that Excel considers a Row with a height of about
0.6 or less as "Hidden."
Fortunately, a height of about 0.6 is rather narrow, but Excel does
considered it "hidden."

You can test some ideas with your display with this:

Sub Demo()
Dim H
For H = 0 To 1 Step 0.1
With [A10].EntireRow
.RowHeight = H
Debug.Print _
Format(H, "0.0 - "); Format(.RowHeight, "0.00 "); .Hidden
End With
Next H
End Sub

Here are my results:

0.0 - 0.00 True
0.1 - 0.25 True
0.2 - 0.25 True
0.3 - 0.50 True
0.4 - 0.50 True
0.5 - 0.50 True
0.6 - 0.50 True
0.7 - 0.75 False
0.8 - 0.75 False
0.9 - 1.00 False
1.0 - 1.00 False

My RowHeight gets rounded to the nearest 0.25 value.

If you want, you can adjust Tom's excellent idea with the following...

If Not rng1 Is Nothing Then
sStr = rng1.Address(False, False)
Debug.Print sStr
Else
MsgBox "No visible rows"
End If

Prints the string:
A10,A16,A18,A22

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"TonyJeffs" wrote in message
om...
Thanks for the explanations.
I understand it now.
Hopefully one day, this'll all become intuitive to me!

Tony