How to check row is hidden when apply filter
On 2月11日, 下午9時24分, Dave Peterson wrote:
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
moonhk wrote:
On 2月11æ¥, ä¸*å ˆ9æ17åˆ*, Dave Peterson wrote:
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
moonhk wrote:
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
--
Dave Peterson
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
--
Dave Peterson- 隱藏被引用文* -
- 顯示被引用文* -
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(xlCellType Visible) = 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
|