Advance Filter Error in Excel VBA.

[Mohammad Ishaq]

Hello Guys,

I have an excel file having two sheets.First sheet is for main data entry and the second sheet is for advance filter.
I want to use advance filter to transfer data to the second sheet through a command button on the second sheet.
For this purpose i have write a macro which is given below.

Option Explicit

Sub Filter_Transportation()

    Sheets("Transportation").Range("C8").CurrentRegion.AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Advance Filter").Range("C6:C7"), _
        CopyToRange:=Sheets("Advance Filter").Range("F6:W6") _
        , Unique:=False
End Sub

But when i run the micro , it gives me an error.I can't understand how to solve this problem.My file is in attachment .Please help me to solve my problem

Thanks to everyone

 

[Marc L]

Hi !

Check first what returns CurrentRegion.Address
as the issue 'cause it does not start in row #8 but in row #6 !
Maybe try with an Offset or from row #8 to the last row …

As a smart data worksheet starts directly with headers in row #1.
​

 

[Hui]

You have some characters or non-blank cells somewhere leading back to row 6

I would change the code slightly to avoid the issue:

Sub Filter_Transportation()

  Sheets("Transportation").Activate
 
  Dim lr As Integer
  lr = Range("C" & Rows.Count).End(xlUp).Row
 
  Dim myAdd As String
  myAdd = "B8:T" & CStr(lr)
 
  'Sheets("Advance Filter").Activate
 
  Sheets("Transportation").Range(myAdd).AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Advance Filter").Range("C6:C7"), _
        CopyToRange:=Sheets("Advance Filter").Range("E6:W6"), _
        Unique:=False
End Sub

 

[NARAYANK991]

Hi ,

The problem is that the CurrentRegion method returns the address B6:U130 , whereas your data should be in the region B8:T130.

The reasons for this are two :

In rows 6 and 7 you have the formula in cell H6 and the title in cell K7.

Then you have the FALSE value in some cells in column U. Since this column of data does not have a header in cell U6 , the AdvancedFilter method is returning the error you have posted.

In both cases , the font color has been made the same as the background color , which makes it difficult to see at first glance.

Once you remove these values and clear the contents of column U , the CurrentRegion method will return the correct data range , and everything will work correctly.

Otherwise , instead of using the CurrentRegion method , specify the address directly in the code , as B8:T130.

Narayan