Having selected a number of cells, I use the following to process them:

Sub Demo1()
Dim myCell
For Each myCell In Selection
‘Do some processing:
Debug.Print myCell.Value
Next myCell
End Sub

This works fine until I select cells across cells hidden by the autofilter
The above macro includes the cells hidden by the autofilter which I do not
want.
I amend my code as follows:

Sub Demo2()
Dim myCell
For Each myCell In Selection
If Not myCell.Hidden Then ‘this line fails – 1004 Unable to get the
Hidden property of the range class
‘Do some processing:
Debug.Print myCell.Value
End If
Next myCell
End Sub

I have used debug watch on myCell and Selection and cannot find an
alternative way of skipping the autofiltered cells.
Can anyone help please?

if mycell.entirerow.hidden = false then
'do your stuff
...
end if

Or you could look at just the visible cells in the selection

dim vRng as range
set vrng = nothing
on error resume next
set vrng = intersect(selection,selection.cells.specialcells(xlcelltypevisible)
on error goto 0

if vrng is nothing then
'no cells are visible!
else
for each mycell in vrng.cells
'do something
next mycell
end if

Lionel H wrote:
>
> Having selected a number of cells, I use the following to process them:
>
> Sub Demo1()
> Dim myCell
> For Each myCell In Selection
> ‘Do some processing:
> Debug.Print myCell.Value
> Next myCell
> End Sub
>
> This works fine until I select cells across cells hidden by the autofilter
> The above macro includes the cells hidden by the autofilter which I do not
> want.
> I amend my code as follows:
>
> Sub Demo2()
> Dim myCell
> For Each myCell In Selection
> If Not myCell.Hidden Then ‘this line fails – 1004 Unable to get the
> Hidden property of the range class
> ‘Do some processing:
> Debug.Print myCell.Value
> End If
> Next myCell
> End Sub
>
> I have used debug watch on myCell and Selection and cannot find an
> alternative way of skipping the autofiltered cells.
> Can anyone help please?

--

Dave Peterson

Someone else came up with this which should work:

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For i = 1 To nLastRow
If Cells(i, "A").EntireRow.Hidden Then
Else
'perform your task!
End If
Next

Dave,
Thanks for the reply.
your first offering works, is simplest and therefore the best.
I thought I had tried this but looking back through my tests,
I find I had used:
If not selection.entirerow.hidden then
DOH!
Thanks again.
Lionel

"Dave Peterson" wrote:

> if mycell.entirerow.hidden = false then
> 'do your stuff
> ...
> end if
>
> Or you could look at just the visible cells in the selection
>
> dim vRng as range
> set vrng = nothing
> on error resume next
> set vrng = intersect(selection,selection.cells.specialcells(xlcelltypevisible)
> on error goto 0
>
> if vrng is nothing then
> 'no cells are visible!
> else
> for each mycell in vrng.cells
> 'do something
> next mycell
> end if
>
> Lionel H wrote:
> >
> > Having selected a number of cells, I use the following to process them:
> >
> > Sub Demo1()
> > Dim myCell
> > For Each myCell In Selection
> > ‘Do some processing:
> > Debug.Print myCell.Value
> > Next myCell
> > End Sub
> >
> > This works fine until I select cells across cells hidden by the autofilter
> > The above macro includes the cells hidden by the autofilter which I do not
> > want.
> > I amend my code as follows:
> >
> > Sub Demo2()
> > Dim myCell
> > For Each myCell In Selection
> > If Not myCell.Hidden Then ‘this line fails – 1004 Unable to get the
> > Hidden property of the range class
> > ‘Do some processing:
> > Debug.Print myCell.Value
> > End If
> > Next myCell
> > End Sub
> >
> > I have used debug watch on myCell and Selection and cannot find an
> > alternative way of skipping the autofiltered cells.
> > Can anyone help please?
>
> --
>
> Dave Peterson
>

Mike,
Deve Peterson got there first, so I used his solution, but thanks for the
response anyway.
(See also my reply to Dave!)
regards,
Lionel

"Mike H." wrote:

> Someone else came up with this which should work:
>
> Set r = ActiveSheet.UsedRange
> nLastRow = r.Rows.Count + r.Row - 1
>
> For i = 1 To nLastRow
> If Cells(i, "A").EntireRow.Hidden Then
> Else
> 'perform your task!
> End If
> Next
>
>

That first suggestion may be the easiest to type, but it could take the longest
to run.

If you have 65k rows of info and only the 2nd and 65kth row are visible, why
check each of them.


Lionel H wrote:
>
> Dave,
> Thanks for the reply.
> your first offering works, is simplest and therefore the best.
> I thought I had tried this but looking back through my tests,
> I find I had used:
> If not selection.entirerow.hidden then
> DOH!
> Thanks again.
> Lionel
>
> "Dave Peterson" wrote:
>
> > if mycell.entirerow.hidden = false then
> > 'do your stuff
> > ...
> > end if
> >
> > Or you could look at just the visible cells in the selection
> >
> > dim vRng as range
> > set vrng = nothing
> > on error resume next
> > set vrng = intersect(selection,selection.cells.specialcells(xlcelltypevisible)
> > on error goto 0
> >
> > if vrng is nothing then
> > 'no cells are visible!
> > else
> > for each mycell in vrng.cells
> > 'do something
> > next mycell
> > end if
> >
> > Lionel H wrote:
> > >
> > > Having selected a number of cells, I use the following to process them:
> > >
> > > Sub Demo1()
> > > Dim myCell
> > > For Each myCell In Selection
> > > ‘Do some processing:
> > > Debug.Print myCell.Value
> > > Next myCell
> > > End Sub
> > >
> > > This works fine until I select cells across cells hidden by the autofilter
> > > The above macro includes the cells hidden by the autofilter which I do not
> > > want.
> > > I amend my code as follows:
> > >
> > > Sub Demo2()
> > > Dim myCell
> > > For Each myCell In Selection
> > > If Not myCell.Hidden Then ‘this line fails – 1004 Unable to get the
> > > Hidden property of the range class
> > > ‘Do some processing:
> > > Debug.Print myCell.Value
> > > End If
> > > Next myCell
> > > End Sub
> > >
> > > I have used debug watch on myCell and Selection and cannot find an
> > > alternative way of skipping the autofiltered cells.
> > > Can anyone help please?
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson