Answered by:
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
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.RangeWhen 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.basAndreas.
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.RangeWhen 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