Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofiltered cells not "Hidden"
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofiltered cells not "Hidden"
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(x lcelltypevisible) 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofiltered cells not "Hidden"
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofiltered cells not "Hidden"
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(x lcelltypevisible) 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofiltered cells not "Hidden"
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofiltered cells not "Hidden"
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(x lcelltypevisible) 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Difficulty "countif"-ing number of "L1" or "L2" on an autofiltered | Excel Worksheet Functions | |||
Reverting all Autofiltered columns to "ALL" | Excel Discussion (Misc queries) | |||
"ignoring" hidden cells in a copy/paste operation | Excel Discussion (Misc queries) | |||
Hi-Lite "empty" unlocked cells in non-hidden rows | Excel Programming | |||
Enter a "checkmark" in an AutoFiltered cell | Excel Programming |