VBA Auto Filter

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,501
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

I am using a VBA code to filter record. Here is the code

Sub aaa()

ActiveSheet.Range("A1:B25").AutoFilter Field:=1, Criteria1:=Range("C1"), _
Operator:=xlOr

ActiveSheet.Range("A1:B25").AutoFilter Field:=2, Criteria1:="*a*", _
Operator:=xlAnd

End Sub
Click to expand...

I would like to give a range (D1) in the Field:=2 part of the code instead of "a"

Any help will be appreciated

Regards,

Humayun
 
Sort by date Sort by votes
Hi Humayun,

Try changing the sum value in the last line of code from -4 to:
.Rows(lngRow).Hiden = ((blnA + blnB + ... + blnV) > -22)

Each Boolean is checking if a given condition is met. If all of them are met, the sum of all booleans will give you -22; we want to hide a row if any of these conditions is not met (i.e. the sum of all 22 booleans is lower than -22).
Hope it helps.
 
Upvote 0

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi,

I did that... but its hiding all the rows asa the macro is triggered
 
Upvote 0
Hi,

Can you run the following test macro on your Sheet:

Code: 
 Sub test()
    Dim lngRow              As Long
    Dim lngColumn           As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
        For lngRow = 4 To 1000
            For lngColumn = 1 To 22
                If InStr(UCase(.Cells(lngRow, lngColumn).Value), UCase(.Cells(2, lngColumn).Value)) Then
                    .Cells(lngRow, lngColumn).Interior.ColorIndex = 14
                End If
            Next lngColumn
        Next lngRow
    End With
End Sub

It will change Interior Color of each cell if it meets the condition (i.e. part of the string contains a character(s) specified in row 2). Let me know if any of the rows contain all 22 cells that are highlighted.
 
Last edited:
Upvote 0
sure, why not....
Just tried it and found the following

1) Its not looking at the criteria in row 2.... Just Highlighting all the data from row 4:1000 as soon as the macro runs.
 
Last edited:
Upvote 0
Can you copy & paste row 2 and the next 5-10 rows please? I'll try to recreate this issue on my side.
 
Upvote 0
Sure, i will in few minutes

Just wanted to let u know that the data in Range A4:V1000 are all results of formula.
 
Upvote 0
DATABASE TRACKING SYSTEM
123HumayunBed Sets
P.O #REFP.O.DATECustomerSuppliersArticleQualityDyed or PrintedFiber ContentConstructionSize QuantityUNITP.O. SHIPMENT DATEActual Ship DATERemarksDesigns/ Pantone From CustomerP.P Samples Submition DateDays From Design To Sample SubmissionApproval Date ValuesSTATUS
9582212331-Oct-13ABCHumayunBed SetsCVC Renforce - 90 GsmPigment Printed Rotary60% Cotton 40% Polyester100D X 24 Ctn 76X52Normal 676,830Set(s)20-Jan-1429-Jan-14 $ 3,569.00Shipped
9582512331-Oct-13ABCHumayunBed SetsCVC Renforce - 90 GsmPigment Printed Rotary60% Cotton 40% Polyester100D X 24 Ctn 76X52Over 201,520Set(s)20-Jan-1429-Jan-14 $ 1,256.00Shipped
9582812331-Oct-13ABCHumayunBed SetsCVC Renforce - 90 GsmPigment Printed Rotary60% Cotton 40% Polyester100D X 24 Ctn 76X52King 15,420Set(s)20-Jan-1429-Jan-14 $ 3,498.00Shipped
9583012331-Oct-13ABCBushSide PillowCVC Renforce - 90 GsmPigment Printed Rotary60% Cotton 40% Polyester100D X 24 Ctn 76X5240x145 179,712Pc(s)20-Jan-1429-Jan-14 $ 1,263.00Shipped
9583112331-Oct-13DEFBushPillow PairCVC Renforce - 90 GsmPigment Printed Rotary60% Cotton 40% Polyester100D X 24 Ctn 76X5240x80 436,032Pair(s)20-Jan-1429-Jan-14 $ 1,236.00Shipped
4004154112727-Sep-13DEFHumayunTerry TowelsCTN 400 Gsm PUNCHReactive Dyed100% CottonMultiple 58,000Pc(s)18-Mar-14Best Price Terry $ 1,698.00Shipped
4004322712713-Feb-14DEFMichaelTerry TowelsCTN 400 Gsm PUNCHReactive Dyed100% CottonMultiple 1,887Pc(s)25-Mar-14Best Price Terry $ 1,236.00Shipped
400420451297-Nov-13DEFMichaelBed SetsCTN Renforce - 115 GsmPigment Printed Rotary50/50 Poly / Cotton30X30 / 76X68100x135 + 40x60 2,400Set(s)28-Jan-14Lola / Pepito $ 1,236.00Shipped
12943122 A13011-Dec-13GHIMichaelPillow PcCTN Renforce - 115 GsmReactive Dyed100% Cotton30X30 / 76X68Multiple 19,400Pc(s)18-Feb-14Lindra $ 1,236.00Shipped
12943122 B13011-Dec-13GHIBushDuvet CoverCTN Renforce - 115 GsmReactive Dyed100% Cotton30X30 / 76X68Multiple 6,000Pc(s)18-Feb-14Lindra $ 157.00Shipped
67257113121-Nov-13GHIBushFitted SheetInterlock 135 GSMReactive Dyed100% Polyester30'S CottonMultiple 2,500Pc(s)20-Jan-14 $ 19,876.00Shipped

<tbody>
</tbody>


From Row A1:V14
 
Upvote 0
Thanks.
It looks like macro has some issues with "empty" columns (like "P.P Samples Submition Date" or "Days From Design To Sample Submission").
Try to modify each of blnA - blnV elements to accommodate for empty columns, as per example below:
blnA = InStr(UCase(.Range("A" & lngRow).Value), UCase(.Range("A2").Value)) Or (Len(.Range("A2").Value) = 0)
 
Upvote 0
Hi,

i was about the write the same thing... its the empty columns
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
VBA To Filter Conditionally Formatted coloured cells in multiple columns
Replies
1
Views
169
bobsan42
bobsan42
Waving Not Drowning
  • Solved
Filter criteria range from two cells including in VBA macro
Replies
3
Views
122
Alex Blakenburg
A
airforceone
  • Solved
AutoFilter Working but not on Date
Replies
5
Views
637
Alex Blakenburg
A
C
  • Question
vba autofilter across multiple columns
Replies
1
Views
21
Artik
Artik
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
532
Alex Blakenburg
A

Forum statistics

Threads
1,215,219
Messages
6,123,691
Members
449,117
Latest member
Aaagu

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