none
Filter Range Error RRS feed

  • Question

  • Hi

    I am not sure if anyone can help me here but here goes...

    I have the following 2 lines of code as follows:

    Sheet29.Range("BUSGRP_TABLE").AutoFilter Field:=1, Criteria1:=Sheet9.Range("SBFY_BusGrp")  

     Set FltrRng = Sheet29.AutoFilter.Range

    The first lines filters on field 1. The BUSGRP_TABLE is a name which defines the range to be filetered; the SBFY_BusGRP is a name which specifies the criteria.

    this line works ok - if I change the values it still works.

    the second  is supposed to set the FltrRng to the range of filtered data from line 1.  This would allow me to manipulate the filtered data and copy it to another worksheet.

    I keep getting error with this

    "Run time error 91 - object variable or With block variable not set"

    I really do not understand what this means.

    I have 2 lines of code very similar in another procedure and this works fine - so I do not understand why this does not work.

    any suggestion would be welcome - or an alternative way to do this would also be welcome.

    many thanks!

    peter

    Wednesday, December 16, 2015 10:21 AM

Answers

  • I have highlighted in the code the line that seems to fail all the time.

    Yes, because you don't have an Autofilter in the sheet, the Autofilter is inside the ListObject (the table).

    This would work:
    Set FltrRng = Sheet1.ListObjects(1).AutoFilter.Range

    When you have a ListObject in a sheet, the code becomes a little different. replace your code with the code below.

    Andreas.

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim FltrRng As Range, Dest As Range, Last As Range
      'Only if our dropdown is changed
      If Target.Address = Range("SBFY_BusGrp").Address Then
        'Access the table in the other sheet
        With Sheet1.ListObjects("Table_owssvr_1")
          With .AutoFilter
            'Clear existing filters
            .ShowAllData
            'Set our own
            .Range.AutoFilter 1, Range("SBFY_BusGrp")
          End With
          'Get the filter result if any
          Set FltrRng = .ListColumns("Business Group Level 2").DataBodyRange
          If FltrRng Is Nothing Then Exit Sub
    
          'Find an empty cell from the bottom
          Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(2)
          'Copy the data
          Application.EnableEvents = False
          FltrRng.Copy
          Dest.PasteSpecial xlPasteValues
          Application.EnableEvents = True
          'Create the name over the copied cells
          Set Last = Range("A" & Rows.Count).End(xlUp)
          Range(Dest, Last).Name = ValidName(Range("SBFY_BusGrp"))
        End With
      End If
    End Sub
    
    Private Function ValidName(ByVal Name As String) As String
      'Replaces invalid chars in Name with "_"
      Dim i As Long, Digit As Integer
      For i = 1 To Len(Name)
        Digit = Asc(Mid$(Name, i, 1))
        Select Case Digit
          Case 48 To 57, 65 To 90, 97 To 122
            '0 to 9, A to Z, a to z
          Case Else
            Mid$(Name, i, 1) = "_"
        End Select
      Next
      Select Case Len(Name)
        Case 0
          Name = "_"
        Case 1
          Select Case UCase(Name)
            Case "0" To "9"
              Name = "_" & Name
            Case "R", Application.International(xlUpperCaseRowLetter), _
                "C", Application.International(xlUpperCaseColumnLetter)
              Name = Name & "_"
          End Select
      End Select
      ValidName = Name
    End Function
    

    • Marked as answer by py1 Tuesday, December 22, 2015 1:10 PM
    Monday, December 21, 2015 6:06 PM

All replies

  • I have 2 lines of code very similar in another procedure and this works fine - so I do not understand why this does not work.

    The issue is not related to the code lines, it depends on the layout of your file.

    If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    Andreas.

    Wednesday, December 16, 2015 6:19 PM
  • According to your description, I have made a similar sample to try to reproduce your issue, unfortunately, I can't. So I agree with Andreas Killer's reply, this issue maybe is related to your excel file. Could you upload excel file on OneDrive, that will help us reproduce and resolve this issue.

    Thanks for your understanding.

    Thursday, December 17, 2015 6:26 AM
  • Andreas, David

    Unfortunately my file contains confidential data and it is difficult for me to share - are there any clues you could suggest that I look for?

    In the meantime - I will have a look to see if I can do anything to create a version of the file for you.

    thank you.

    Thursday, December 17, 2015 9:11 AM
  • Unfortunately my file contains confidential data and it is difficult for me to share

    A macro to anonymize the data in selected cells can be downloaded here:
    https://dl.dropboxusercontent.com/u/35239054/modAnonymize.bas

    Andreas.

    Thursday, December 17, 2015 11:03 AM
  • Andreas

    I have recreated the problem in a simple file and uploaded this  to Dropbox - link below.

    https://www.dropbox.com/s/iux45w6n3a5hu0k/Filter%20%26%20Drop%20Down%20v2.xlsm?dl=0

    I have highlighted in the code the line that seems to fail all the time.

    All I want to be anle to do is this:

    Filter the table on column A;

    Once the table is filtered , copy the column B into a separate sheet;

    Set up a name for the values that are in the separate sheet.

    In this example, if "Enterprise Group" is selected  then the single value "GRE" would be copied to another sheet and a name assigned to this.

    If "Enterprise Services" is selected then  there are 7 values copied to antoher sheet and a name is assigned to the 7 values.

    I hope this makes sense.

    thank you.

    Monday, December 21, 2015 2:15 PM
  • I have highlighted in the code the line that seems to fail all the time.

    Yes, because you don't have an Autofilter in the sheet, the Autofilter is inside the ListObject (the table).

    This would work:
    Set FltrRng = Sheet1.ListObjects(1).AutoFilter.Range

    When you have a ListObject in a sheet, the code becomes a little different. replace your code with the code below.

    Andreas.

    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim FltrRng As Range, Dest As Range, Last As Range
      'Only if our dropdown is changed
      If Target.Address = Range("SBFY_BusGrp").Address Then
        'Access the table in the other sheet
        With Sheet1.ListObjects("Table_owssvr_1")
          With .AutoFilter
            'Clear existing filters
            .ShowAllData
            'Set our own
            .Range.AutoFilter 1, Range("SBFY_BusGrp")
          End With
          'Get the filter result if any
          Set FltrRng = .ListColumns("Business Group Level 2").DataBodyRange
          If FltrRng Is Nothing Then Exit Sub
    
          'Find an empty cell from the bottom
          Set Dest = Range("A" & Rows.Count).End(xlUp).Offset(2)
          'Copy the data
          Application.EnableEvents = False
          FltrRng.Copy
          Dest.PasteSpecial xlPasteValues
          Application.EnableEvents = True
          'Create the name over the copied cells
          Set Last = Range("A" & Rows.Count).End(xlUp)
          Range(Dest, Last).Name = ValidName(Range("SBFY_BusGrp"))
        End With
      End If
    End Sub
    
    Private Function ValidName(ByVal Name As String) As String
      'Replaces invalid chars in Name with "_"
      Dim i As Long, Digit As Integer
      For i = 1 To Len(Name)
        Digit = Asc(Mid$(Name, i, 1))
        Select Case Digit
          Case 48 To 57, 65 To 90, 97 To 122
            '0 to 9, A to Z, a to z
          Case Else
            Mid$(Name, i, 1) = "_"
        End Select
      Next
      Select Case Len(Name)
        Case 0
          Name = "_"
        Case 1
          Select Case UCase(Name)
            Case "0" To "9"
              Name = "_" & Name
            Case "R", Application.International(xlUpperCaseRowLetter), _
                "C", Application.International(xlUpperCaseColumnLetter)
              Name = Name & "_"
          End Select
      End Select
      ValidName = Name
    End Function
    

    • Marked as answer by py1 Tuesday, December 22, 2015 1:10 PM
    Monday, December 21, 2015 6:06 PM
  • Andreas

    Thank you for this comprehensive answer it has certainly helped me.

    As a general point..... I am 'self taught' at Excel VBA and have just been picking up skills as I have gone a long - are you able to point  me in the direction of any useful reference material (books and so on). I find the MS help useful only to a point.

    May I wish you a Merry Xmas and thanks for all your help this year.

    regards,

    Peter

    Tuesday, December 22, 2015 1:13 PM