| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to check row is hidden when apply filter
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check row is hidden when apply filter
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check row is hidden when apply filter
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check row is hidden when apply filter
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 -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" "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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check row is hidden when apply filter
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check row is hidden when apply filter
I don't see where you're checking the .hidden property of any single cell's row.
moonhk wrote: 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 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check row is hidden when apply filter
On Feb 12, 9:53*pm, Dave Peterson wrote:
I don't see where you're checking the .hidden property of any single cell's row. moonhk wrote: 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 -- Dave Peterson 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to check row is hidden when apply filter
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 moonhk wrote: On Feb 12, 9:53 pm, Dave Peterson wrote: I don't see where you're checking the .hidden property of any single cell's row. moonhk wrote: 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 -- Dave Peterson 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 -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determine filtered row in excel table with autofilter
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 On Wednesday, February 10, 2010 8:02 PM 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 On Wednesday, February 10, 2010 8:17 PM 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 does not 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: -- Dave Peterson On Thursday, February 11, 2010 12:00 AM moonhk wrote: On 2=E6=9C=8811=E6=97=A5, =E4=B8=8A=E5=8D=889=E6=99=8217=E5=88=86, Dave Pet= ells.count then all the worksheet 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 =3D 1 To iRows .... Next moonhkt On Thursday, February 11, 2010 7:08 AM K_Macd wrote: 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 -- Ken "Using Dbase dialects since 82" "Started with Visicalc in the same year" "moonhk" wrote: On Thursday, February 11, 2010 8:24 AM Dave Peterson wrote: You could check the .hidden property of the cell or you could just loop through the visible cells. Here is 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 Friday, February 12, 2010 1:28 AM moonhk wrote: On 2=E6=9C=8811=E6=97=A5, =E4=B8=8B=E5=8D=889=E6=99=8224=E5=88=86, Dave Pet= hrough , "A").End(xlUp)) mevalue" peVisible) =3D 1 Then er visible" esize(.Cells.Count - 1).Offset(1, 0) _ =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 .Cells.SpecialCells(xlCellTypeVisibl= e) n VisRng.Cells Box myCell.Address 'or whatever you need to do =CB=869=C3=A6=E2=84=A2=E2=80=9A17=C3=A5=CB=86=E2=8 0=A0, Dave Peterson <pet= evisible).cells.count then that all le. e =3D true then =82 'there are autofilter arrows on the worksheet =82 if .filtermode then =82 =C2=A0=C3=82 =C2=A0'some filter is applied =82 =C2=A0=C3=82 =C2=A0'maybe show all the data =82 =C2=A0=C3=82 =C2=A0.showalldata =C3=82 '???? =82 end if On Friday, February 12, 2010 8:53 AM Dave Peterson wrote: I do not see where you are checking the .hidden property of any single cell's row. moonhk wrote: On Saturday, February 13, 2010 2:30 AM moonhk wrote: 's row. op through unt, "A").End(xlUp)) "somevalue" lTypeVisible) =3D 1 Then eader visible" der .Resize(.Cells.Count - 1).Offset(1, 0) _ =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0.Cells.SpecialCells(xlCellTypeVis= ible) l In VisRng.Cells MsgBox myCell.Address 'or whatever you need to do er =CB=869=C3=A6=E2=84=A2=E2=80=9A17=C3=A5=CB=86 , Dave Peterson <peter...@ver= izonXSPAM.net wrote: ge: isible).cells.count then be that all isible. rrows on the worksheet applied the data =82 '???? On Saturday, February 13, 2010 8:23 AM Dave Peterson wrote: Set Rng_hidden = nothing on error resume next Set Rng_hidden = yourlongexpression on error goto 0 if rng_hidden is nothing then 'it is hidden else 'it is not end if moonhk wrote: Submitted via EggHeadCafe - Software Developer Portal of Choice ObjectDumper LINQ To Export Collection Via .NET Reflection http://www.eggheadcafe.com/tutorials...eflection.aspx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| msg box to apply filter | Excel Programming | |||
| Apply same filter to all sheets in a workbook | Excel Programming | |||
| Apply format with check box | Excel Programming | |||
| Apply Auto Filter on One Column | Excel Programming | |||
| Macro or VB to apply filter in spreadsheet | Excel Programming | |||
![moonhk[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
