Geek asks and answers

Does vba care about autofilter?

If I apply an autofilter to my input sheet and then run VBA code, the code will not care about the autofilter.

But sometimes running VBA code on an automatically filtered worksheet spoils the results of a program.

So my question is: does VBA care about autofilter?

For instance:

Sub check()
    Dim rng as range
    Set rng = Sheets("input").Range("A1")
    row = 0
    Do until rng.offset(row,0) = ""
        row = row + 1
    Loop
End Sub

In the above code, VBA doesn't care if the autofilter is applied to column A and it still iterates over all the rows. However, if I try to write to cells where there is an autofilter, it spoils.

+3
source share
1 answer

VBA does not care about Autofilter unless you “say” it or try to perform actions that might be affected by autofilter.

, .

, ( )

'~~> Remove any filters
ActiveSheet.AutoFilterMode = False

'~~> Filter, offset(to exclude headers) and delete visible rows   
With rRange 
  .AutoFilter Field:=1, Criteria1:=strCriteria
  .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

'~~> Remove any filters
ActiveSheet.AutoFilterMode = False

, .

, Autofilter. , . VBA Non VBA .

  

, , .

  

, .

. , . .

Option Explicit

Sub Sample()
    Dim rng As Range

    Set rng = Sheets("Sheet1").Range("A1")

    rng.AutoFilter Field:=1, Criteria1:="<>1", Operator:=xlAnd

    rng.Offset(1, 0).Value = "Sidd"
End Sub

enter image description here

. . , A2 A10 (A1 ), 1 3. A2: A10, 1000. , . .

Option Explicit

Sub Sample()
    Dim rng As Range

    Set rng = Sheets("Sheet1").Range("A1:A10")

    rng.AutoFilter Field:=1, Criteria1:="<>1", Operator:=xlAnd

    rng.Value = "1000"
End Sub

, "1" ( , ) ? ?

enter image description here

. Autofilter - ( , < > 1). rng, , ( ) .

, ?

1) - -

Sub Sample()
    Dim rng As Range

    Set rng = Sheets("Sheet1").Range("A1:A10")

    '~~> Put Filter
    rng.AutoFilter Field:=1, Criteria1:="<>1", Operator:=xlAnd

    '~~> Remove Filter
    ActiveSheet.AutoFilterMode = False

    '~~> Write value to the cells (See how we ignore the header)
    Sheets("Sheet1").Range("A2:A10").Value = "1000"

    '~~> Put Filter back
    rng.AutoFilter Field:=1, Criteria1:="<>1", Operator:=xlAnd
End Sub

2) ,

Sub Sample()
    Dim rng As Range, cl As Range

    Set rng = Sheets("Sheet2").Range("A1:A10")

    rng.AutoFilter Field:=1, Criteria1:="<>1", Operator:=xlAnd

    For Each cl In rng
        '~~> Ignoring the Header
        If cl.Row <> 1 then _
        cl.Value = "1000"
    Next
End Sub

, .

Excel, , AutoFilters. , , , , .

+7
source

More articles:


All Articles