How to check row is hidden when apply filter

  • Thread starter Thread starter moonhk
  • Start date Start date
M

moonhk

Hi All

For Excel 2003
When Apply Filter , How to check the row have been filtered out ?

Below coding include filtered out row.

iRows = Application.Selection.Rows.Count
iCols = Application.Selection.Columns.Count


For ir = 1 To iRows
.....
Next
 
You can count the visible cells in a column in the autofilter range:

With activesheet.autofilter.range.columns(1)
if .cells.count = .cells.specialcells(xlcelltypevisible).cells.count then
'all visible
else
'not all visible
end if
end with

But that doesn't tell you if the data has been filtered--it could be that all
the cells in the filter meet the criteria, so that all rows are visible.

If that is important, you may want to check something like:

With activesheet
if .autofiltermode = true then
'there are autofilter arrows on the worksheet
if .filtermode then
'some filter is applied
'maybe show all the data
.showalldata '????
end if
end if
end with
 
You can count the visible cells in a column in the autofilter range:

With activesheet.autofilter.range.columns(1)
   if .cells.count = .cells.specialcells(xlcelltypevisible).cells.count then
     'all visible
   else
     'not all visible
   end if
end with

But that doesn't tell you if the data has been filtered--it could be thatall
the cells in the filter meet the criteria, so that all rows are visible.

If that is important, you may want to check something like:

    With activesheet
        if .autofiltermode = true then
           'there are autofilter arrows on the worksheet
           if .filtermode then
              'some filter is applied
              'maybe show all the data
              .showalldata  '????
           end if
        end if
    end with
Click to expand...

Hi All

When Apply filter , some row have been hidden, When I select some row
to process. the hidden also selected. How to check the row is filter
out by filters ?

e.g.
line 1
line 2 <- Filter out
line 3

When select line1 to line3 , the filter out still selected within
blelow for loop

For ir = 1 To iRows
.....
Next





moonhkt
 
What you see in interactive name is not the same way that VBA operates. To
emulate a filter to have to expressly put a test condition within your
For/Next block to ignore those rows excluded by the filter.

1. Inconsistent !
2. Excessive coding !

Good luck
 
You could check the .hidden property of the cell or you could just loop through
the visible cells.

Here's a sample of the second:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim VisRng As Range
Dim myRng As Range
Dim myCell As Range

Set wks = ActiveSheet

With wks
'just a single column
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

'remove any existing filter
.AutoFilterMode = False
myRng.AutoFilter Field:=1, Criteria1:="somevalue"

With .AutoFilter.Range.Columns(1)
If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then
MsgBox "only header visible"
Else
'avoid the header
Set VisRng = .Resize(.Cells.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
For Each myCell In VisRng.Cells
MsgBox myCell.Address 'or whatever you need to do
Next myCell
End If
End With
.AutoFilterMode = False 'remove the filter
End With
End Sub
 
You could check the .hidden property of the cell or you could just loop through
the visible cells.

Here's a sample of the second:

Option Explicit
Sub testme()

    Dim wks As Worksheet
    Dim VisRng As Range
    Dim myRng As Range
    Dim myCell As Range

    Set wks = ActiveSheet

    With wks
        'just a single column
        Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

        'remove any existing filter
        .AutoFilterMode = False
        myRng.AutoFilter Field:=1, Criteria1:="somevalue"

        With .AutoFilter.Range.Columns(1)
            If .Cells.SpecialCells(xlCellTypeVisible) = 1 Then
                MsgBox "only header visible"
            Else
                'avoid the header
                Set VisRng = .Resize(.Cells.Count - 1).Offset(1, 0) _
                                .Cells.SpecialCells(xlCellTypeVisible)
                For Each myCell In VisRng.Cells
                    MsgBox myCell.Address 'or whatever you need to do
                Next myCell
            End If
        End With
        .AutoFilterMode = False 'remove the filter
    End With
End Sub












--

Dave Peterson- éš±è—被引用文字 -

- 顯示被引用文字 -
Click to expand...


FYI
I am using check The row is hidden or not.

On Error Resume Next

Set Rng_hidden = Application.Selection.Range(ir & ":" &
ir).SpecialCells(xlCellTypeVisible)
If Rng_hidden Is Nothing Then
'~~ MsgBox Application.Selection.Range(GPD.FM_Name &
ir).Value
CanProcess = False
End If
On Error GoTo 0


iNum = MsgBox("Update to ..." & wksName, vbYesNo + vbQuestion)
If iNum = vbNo Then Exit Sub


iRows = Application.Selection.Rows.Count
iCols = Application.Selection.Columns.Count

cnt_1 = 0
cnt_2 = 0
cnt_3 = 0
cnt_4 = 0
cnt_5 = 0
cnt_6 = 0

For ir = 1 To iRows
'Rows.SpecialCells.Activate
If Application.Selection.Range(GPD.MANU_SYS_ADD & ir).Value =
"PHOENIX" Then
Set Rng_hidden = Nothing
CanProcess = True
On Error Resume Next

Set Rng_hidden = Application.Selection.Range(ir & ":" &
ir).SpecialCells(xlCellTypeVisible)
If Rng_hidden Is Nothing Then
'~~ MsgBox Application.Selection.Range(GPD.FM_Name &
ir).Value
CanProcess = False
End If
On Error GoTo 0
'If Not
Application.Selection.Rows.SpecialCells(xlCellTypeVisible) = 1 Then
'MsgBox Application.Selection.Range(GPD.FM_Name & ir).Value

If CanProcess = True Then

GPD_ID_VAL = Trim(Application.Selection.Item(ir,
GPD.ID_COL).Value)
If Left(GPD_ID_VAL, 1) = "`" Then
GPD_ID_VAL = Trim(Mid(GPD_ID_VAL, 2, Len(GPD_ID_VAL)))
End If
 
I don't see where you're checking the .hidden property of any single cell's row.
Click to expand...

Just using check Rng_hidden row, it set rng_hidden return error. The
selected row is not visual able

Set Rng_hidden = Application.Selection.Range(ir & ":" &
ir).SpecialC
 
Set Rng_hidden = nothing
on error resume next
Set Rng_hidden = yourlongexpression
on error goto 0

if rng_hidden is nothing then
'it's hidden
else
'it's not
end if
 
You cannot use the property "hidden" on a single cell.
You have to check on the complete table row .

Sub test()
Dim table As Range, row As Range
Set table = Range("c1").CurrentRegion ' c1 is part of the table
For Each row In table.Rows ' table header gets processed also. but you get the idea
If row.Hidden Then
Debug.Print row.Cells(1, 1).Address, "is part of a filtered out row"
Else
Debug.Print row.Cells(1, 1).Address, "is not part of a filtered out row"
End If
Next
End Sub

regards,
Pancras
 
Back
Top