CODEWITHSUNDEEP
excelvba
wingkit hui
wingkit hui

Reputation: 27

Filter blank cell value

I am trying to filter the columns which is not blank, however it doesn't work with following code:

 ActiveSheet.Range("A:FE").AutoFilter Field:=12, Criteria1:="<>", Operator:=xlOr

Also please notice that there are 6 rows on top of the header and I can't delete it for other purpose

I don't know will it affect the coding

Upvotes: 1

Views: 63

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149287

If the first 6 rows are blank then do not filter A:FE. Construct your range and then filter it. I guess you want to filter on Col L (Field:=12)?

Try this

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim rng As Range

    Set ws = Sheet1 '<~~ Change this to the relevant sheet

    With ws
        .AutoFilterMode = False

        '~~> Assuming that Col A will have all cells filled up
        '~~> Else change this to the relevant column or use .Find
        '~~> to find the last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Construct your range
        Set rng = Range("A7:FE" & lRow)

        '~~> Filter it
        rng.AutoFilter Field:=12, Criteria1:="<>"
    End With
End Sub

Upvotes: 1

Related Questions