Filter doesn't recognize value

reeser

Board Regular
Joined
May 19, 2006
Messages
215
I am trying to filter data in one sheet depending on a variable in another. But even though I have the same formatting in both sheets it will not recognize the number. I have tried everything from pasting values, to pasting the same formats in both areas, using the F2 option, but nothing seems to work. My single value is in Sheet1 B2. The rest of the data I'm filtering is in Exceptions2 with the filter criteria residing in column A. I have headers in row 1.

Code that is running the process is below:
Code:
Sub Recall_BT()


Sheets("DataEntry").Unprotect
Sheets("Exceptions2").Visible = True

Dim rr As Range, r1 As Range
Dim r2 As Range
Dim r3 As Range
Dim rCell As Variant

rCell = Sheets("Sheet1").Range("g3")


Dim sh As Worksheet
Dim wsCopyTo As Worksheet

Application.ScreenUpdating = False
Set sh = Worksheets("Exceptions2")
sh.Select
sh.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:=rCell
Set rr = ActiveSheet.AutoFilter.Range
Set rr = rr.Offset(1, 0).Resize(rr.Rows.Count - 1, 1)
On Error Resume Next
 Set r1 = rr.SpecialCells(xlVisible)
On Error GoTo 0
If r1 Is Nothing Then
  MsgBox "No visible cells"
  Exit Sub
End If
Set r2 = Intersect(r1.EntireRow, sh.Range("F:L").EntireColumn)
Set r3 = Intersect(r1.EntireRow, sh.Range("N:W").EntireColumn)
Set wsCopyTo = Worksheets("BackTemp1")
r2.Copy
wsCopyTo.Range("I14").PasteSpecial _
       Paste:=xlPasteValues
r3.Copy
wsCopyTo.Range("Q14").PasteSpecial _
       Paste:=xlPasteValues
r2.EntireRow.Delete

Sheets("Exceptions2").Visible = False
Sheets("DataEntry").Protect

Application.ScreenUpdating = True
End Sub
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try:

dim rCell as string

and change:

rCell = Sheets("Sheet1").Range("g3")

to:

rCell = Sheets("Sheet1").Range("g3").text
 
Upvote 0
Is your code giving you an error, or just no results?

If you are trying for a like match make sure that you change the criteria to:

rCell = "*" & Sheets("Sheet1").Range("g3") & "*"
 
Upvote 0
Is your code giving you an error, or just no results?

If you are trying for a like match make sure that you change the criteria to:

rCell = "*" & Sheets("Sheet1").Range("g3") & "*"

It just continues to show this message:

Code:
If r1 Is Nothing Then
  MsgBox "No visible cells"
  Exit Sub

It indicates it didn't find any matching cells, therefore it filters to nothing. I changed it to a string and tried both measures but neither worked.
 
Upvote 0
I've modified a spreadsheet to work with your code, the code runs fine.

Are you certain you want to filter field 1?

Record a macro while you manually filter to make sure there isn't something wrong with your filter selection.

EDIT: Check that... it isn't working now- something is amiss.
 
Upvote 0
Got it.... once you run the macro it deletes the found items... it can only run once!

Because of:
r2.EntireRow.Delete
 
Upvote 0
Got it.... once you run the macro it deletes the found items... it can only run once!

Because of:
r2.EntireRow.Delete

Yeah the values from the found records are pasted as values into another sheet before the delete phase. My problem, is I can't get the filter to match the 2 values so that it can even paste those records.

The Sheet 1 value will vary depending on the number I enter there. That is why I need the filter code to filter for that value so that I don't have to change it each time.
 
Upvote 0
My suggestion is to enter "Break mode" in the macro and put a break mark on the first line after:

sh.Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=rCell
Set rr = ActiveSheet.AutoFilter.Range <<<----------- Break at this line

Now go back to excel and see what it actually filtered, you may be surprised.
 
Upvote 0
It is still filtering to show nothing just like before. Maybe I'll just try re-establishing all the sheets.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top