Excel vba macro autofilter if "does not contain" cell reference. Please help

R

realest25

New Member
Joined
Jun 7, 2022
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Hello dear members,

I want to filter only the dates that are not the same as cell reference B10, but I want the hours to be ignored. In my case I want to keep only the dates 14.08.2022 and 16.08.2022 (hours ignored).

I have this macro but it doesn't do the job:
Sheets("Sheet1").Range("A2").AutoFilter Field:=2, Criteria1:="<>" & Cells(10, 2).Value

Maybe this could be useful? cell reference A1=LEFT(B10,10) ? Thus, it shows the date 15.08.2022

To change to:

Sheets("Sheet1").Range("A2").AutoFilter Field:=2, Criteria1:="<>" & Cells(1, 1).Value

Maybe some filter that "does not contain" cell reference B10 (hours to be ignored) ? Or that "does not contain" cell reference A1 ?

Please, would anybody be willing to help me ?



Tickets.xlsx
AB
115.08.2022TICKETS
2COUNTRYDATE
3ARGENTINA14.08.2022 11:30
4AUSTRALIA14.08.2022 14:45
5BELGIUM14.08.2022 18:00
6BRAZIL15.08.2022 11:30
7CANADA15.08.2022 12:45
8CHILE15.08.2022 14:00
9COLOMBIA15.08.2022 15:30
10COSTA RICA15.08.2022 16:15
11DENMARK15.08.2022 17:00
12ECUADOR15.08.2022 17:45
13FINLAND15.08.2022 18:30
14FRANCE15.08.2022 19:00
15GERMANY15.08.2022 20:15
16GREECE15.08.2022 21:00
17ICELAND15.08.2022 22:30
18ITALY15.08.2022 23:45
19JAPAN16.08.2022 15:30
20USA16.08.2022 19:00
Sheet1
Cell Formulas
RangeFormula
A1A1=LEFT(B10,10)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A3:A20Cell Valuecontains "Stage"textNO
A3:A20Cell Valuecontains "Losers"textNO
A3:A20Cell Valuecontains "Winners"textNO
A3:A20Cell Valuecontains "Promotion"textNO
A3:A20Cell Valuecontains "Promotion Group"textNO
A3:A20Cell Valuecontains "Taca"textNO
A3:A20Cell Valuecontains "Play Offs"textNO
A3:A20Cell Valuecontains "Play Off"textNO
A3:A20Cell Valuecontains "Women"textNO
A3:A20Cell Valuecontains "WORLD"textNO
A3:A20Cell Valuecontains "EUROPE"textNO
A3:A20Cell Valuecontains "EURO"textNO
A3:A20Cell Valuecontains "UEFA"textNO
A3:A20Cell Valuecontains "CONCACAF"textNO
A3:A20Cell Valuecontains "Europa League"textNO
A3:A20Cell Valuecontains "Champions League"textNO
A3:A20Cell Valuecontains "Friendly"textNO
A3:A20Cell Valuecontains "Trophy"textNO
A3:A20Cell Valuecontains "Taça"textNO
A3:A20Cell Valuecontains "Pokal"textNO
A3:A20Cell Valuecontains "Beker"textNO
A3:A20Cell Valuecontains "Coppa"textNO
A3:A20Cell Valuecontains "Coupe"textNO
A3:A20Cell Valuecontains "Copa"textNO
A3:A20Cell Valuecontains "Cup"textNO
A3:A20Cell Valuecontains "Relegation"textNO
A3:A20Cell Valuecontains "Championship Group"textNO
A3:A20Cell Valuecontains "Relegation Group"textNO
 

Attachments

  • Tickets.PNG
    Tickets.PNG
    42 KB · Views: 7

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Sorry, I Forgot To Clear Some Conditional Formatting Rules ! The Correct One Is Below !


Tickets.xlsx
AB
115.08.2022TICKETS
2COUNTRYDATE
3ARGENTINA14.08.2022 11:30
4AUSTRALIA14.08.2022 14:45
5BELGIUM14.08.2022 18:00
6BRAZIL15.08.2022 11:30
7CANADA15.08.2022 12:45
8CHILE15.08.2022 14:00
9COLOMBIA15.08.2022 15:30
10COSTA RICA15.08.2022 16:15
11DENMARK15.08.2022 17:00
12ECUADOR15.08.2022 17:45
13FINLAND15.08.2022 18:30
14FRANCE15.08.2022 19:00
15GERMANY15.08.2022 20:15
16GREECE15.08.2022 21:00
17ICELAND15.08.2022 22:30
18ITALY15.08.2022 23:45
19JAPAN16.08.2022 15:30
20USA16.08.2022 19:00
Sheet1
Cell Formulas
RangeFormula
A1A1=LEFT(B10,10)
 
Upvote 0
Does this do what you want?

VBA Code: 
Range("A2", Range("B" & Rows.Count).End(xlUp)).AutoFilter Field:=2, Criteria1:="<>" & Range("A1").Value & "*"
 
Upvote 0
Solution
This excel contains tickets for future soccer games matches, beginning with august 2022.
 
Upvote 0
I actually edited my post so the code is now slightly different. I did so because I noticed that your image at the bottom of post #1 shows that AutoFilter is turned on in columns A and B.
You may want that revised code?

Also, the Mark as solution option at the right should mark the post that actually contained the solution. :)
 
Upvote 0
I have made a very small change:

Range("B2", Range("B" & Rows.Count).End(xlUp)).AutoFilter Field:=2, Criteria1:="<>" & Range("A1").Value & "*"

Thank you so much for your reply and inspiration sir ! You are great !
 
Upvote 0
It would make more sense to also change that first B2 to A2 - as I have in post 2. The reason is that in post 7 your range appears to be one single column (B) but AutoFilter is saying to filter Field 2 of that range which would appear to make that column C.
You actually have two columns in the AutoFilter in your sheet, it would be better if your code matched that situation. :)
Whilst the post 7 code will work, it does not appear to match your range and may cause confusion down the track if it ever needs modification.
 
Upvote 0
Yes, you are perfectly right !

Range("A2", Range("B" & Rows.Count).End(xlUp)).AutoFilter Field:=2, Criteria1:="<>" & Range("A1").Value & "*"

Thank You Again Sir !
 
Upvote 0
You must log in or register to reply here.

Similar threads

Tim_D
  • Question Question
VBA Structure Best Practices and Code Review
2
Replies
13
Views
6K
dmt32
D
P
  • Question Question
Help copying the contents of a changing cell to a another cell but only once and triggered by a change in another cell
Replies
6
Views
661
kaos_king
K
E
  • Question Question
Excel 2010 using vba AutoFilter to filter table by cell value returns '1004 Autofilter method of Range class failed'
Replies
3
Views
45K
eldarnegrin
E

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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