Solved

Excel VBA Filter resulting in Method Autofilter of Object Range Failed

Posted on 2013-12-18
7
833 Views
Last Modified: 2013-12-19
I have written a VBA command to use a range value in the tab where the DataTable resides as a filter.

I want to perform the filter on the table and then copy the range of data to a another tab.

The Data to be used as a filter is in cells A1:A2 (A1 is the field heading)

The DataTable begins in cell A7 and the length of the table can vary depending on how many records are returned from the data source.

I think what is happening is that if a filter has been previously done, the DataTable is still reflecting the previous filter.

What is syntax to display the whole data table before performing the new filter?

I have attached a screen shot of the DataTable I want to apply the filter to.

Here is the code I am using:

'
'
'   Select Data from MeditechData tab and Name Range
'
'

   
    Sheets("MeditechData").Select
    ActiveWorkbook.Names("DataRange").Delete
    Sheets("MeditechData").Select
    Range("A5").CurrentRegion.Select
   
    ActiveWorkbook.Names.Add Name:="DataRange", RefersTo:=Selection
   
'
'
'   Apply Filter
'
'

    Selection.AutoFilter
    Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1:A2"), Unique:=False
   
'
'
'   Select Filtered Data and copy to clipboard
'
'
    Range("A5").CurrentRegion.Select
    Selection.Copy
   
 '


Thanks

Glen
Denial-FilterDara.jpg
0
Comment
Question by:GPSPOW
  • 3
  • 3
7 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39728274
Can you upload a small portion of your file for testing? You may fake the values.
0
 

Author Comment

by:GPSPOW
ID: 39728284
Here is the file with the data value copied to save space.

Thanks

Glen
Test-Denials.xlsm
0
 
LVL 81

Expert Comment

by:byundt
ID: 39728287
I rewrote your macro to copy the data to Sheet2. I used ShowAllData to undo the Advanced Filter.

Sub FilterIt()
Dim nm As Name
Dim addr As String
'
'   Select Data from MeditechData tab and Name Range
With Sheets("MeditechData")
    addr = .Range("A5").CurrentRegion.Address
    On Error Resume Next
    .ShowAllData
    Set nm = ActiveWorkbook.Names("DataRange")
    On Error GoTo 0
    If nm Is Nothing Then
        Set nm = ActiveWorkbook.Names.Add(Name:="DataRange", RefersTo:="='" & .Name & "'!" & addr)
    Else
        nm.RefersTo = "='" & .Name & "'!" & addr
    End If
    
'   Apply Filter
    .Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        .Range("A1:A2"), Unique:=False

'   Select Filtered Data and copy to clipboard
    nm.RefersToRange.Copy
End With

Worksheets("Sheet2").Range("A5").PasteSpecial xlPasteValues
End Sub

Open in new window

0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39728296
In your actual macro & workbook, I modified the code as follows:

Sub FilterData()
'
' Filer Data and Copy to PT_Lookup tab

'
'
'   Unhide Columns on PT_Lookup tab
'
'


    Sheets("Pt_Lookup").Select
    
    Columns("D:N").Select
    'Range("D4").Activate
    Selection.EntireColumn.Hidden = False
    
'
'
'   Clear Contents in PT_Lookup area
'
'

    
    Range("E7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
'
'
'   Select Data from MeditechData tab and Name Range
'
'

    
    Sheets("MeditechData").Select
    ActiveWorkbook.Names("DataRange").Delete
    Sheets("MeditechData").Select
    Range("A5").CurrentRegion.Select
    
    ActiveWorkbook.Names.Add Name:="DataRange", RefersTo:="=" & Selection.Address
    
'
'
'   Apply Filter
'
'

    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("A1:A2"), Unique:=False
    
'
'
'   Select Filtered Data and copy to clipboard
'
'
    Range("A5").CurrentRegion.Select
    Selection.Copy
    
 '
 '
 '  Select PT_Lookup tab and paste clipboard ccontents
 '
 '
    Sheets("Pt_Lookup").Select
    Range("E7").Select
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  '
  '
  ' Hide PT_Lookup Columns
  '
  '
    Columns("E:M").Select
    'Range("E4").Activate
    Selection.EntireColumn.Hidden = True
End Sub

Open in new window

0
 

Author Closing Comment

by:GPSPOW
ID: 39728327
The first solution did not work.  The second one was perfect.

Thank you very much

Glen
0
 
LVL 81

Expert Comment

by:byundt
ID: 39728390
The first solution did not work.  The second one was perfect.
The first suggestion followed good professional practices, did what you requested in your question, and worked in tests on my sample workbook. It required modification to do the full job in your actual workbook, however, because your macro was also doing things besides the requested named range change, filter & copy.

The second solution worked without needing change in your actual workbook, but it started with a recorded macro and therefore embodied a number of poor coding practices (such as selecting ranges and worksheets, needlessly deleting named ranges).

I've rewritten the second macro following more professional coding practices. It will work unchanged in your posted workbook.
Sub FilterData2()
Dim rgDest As Range
Dim nm As Name
Dim addr As String
Application.ScreenUpdating = False

' Filter Data and Copy to PT_Lookup tab

'           Unhide Columns on PT_Lookup tab
    With Worksheets("Pt_Lookup")
        .Range("D:N").EntireColumn.Hidden = False
'
'           Clear Contents in PT_Lookup area
        Set rgDest = .Range("E7")
        Set rgDest = Range(rgDest, rgDest.End(xlDown))
        Set rgDest = Range(rgDest, rgDest.End(xlToRight))
        rgDest.ClearContents
    End With

'           Select Data from MeditechData tab and Name Range
    With Worksheets("MeditechData")
        addr = "='" & .Name & "'!" & .Range("A5").CurrentRegion.Address
        On Error Resume Next
        Set nm = ActiveWorkbook.Names("DataRange")
        If nm Is Nothing Then Set nm = ActiveWorkbook.Names.Add(Name:="DataRange", RefersTo:=addr)
        On Error GoTo 0
        nm.RefersTo = addr
    
'           Apply Filter
        If .FilterMode Then .ShowAllData
        .Range("DataRange").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=.Range("A1:A2"), Unique:=False
        
'           Select Filtered Data and copy to clipboard
        .Range("A5").CurrentRegion.Copy
    End With
 
 '  Select PT_Lookup tab and paste clipboard ccontents
    rgDest.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
  
  ' Hide PT_Lookup Columns
    rgDest.Worksheet.Range("E:M").EntireColumn.Hidden = True
End Sub

Open in new window

0
 

Author Comment

by:GPSPOW
ID: 39729222
Thanks

I will review the code and implement it for my user.

Glen
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now