Filter Range Error

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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

Answer 7

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