VBA Auto Filter

H

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
1,516
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
[TABLE="width: 2865"]
<tbody>[TR]
[TD="colspan: 6"]DATABASE TRACKING SYSTEM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]123[/TD]
[TD][/TD]
[TD][/TD]
[TD]Humayun[/TD]
[TD]Bed Sets[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]P.O #[/TD]
[TD]REF[/TD]
[TD]P.O.DATE[/TD]
[TD]Customer[/TD]
[TD]Suppliers[/TD]
[TD]Article[/TD]
[TD]Quality[/TD]
[TD]Dyed or Printed[/TD]
[TD]Fiber Content[/TD]
[TD]Construction[/TD]
[TD]Size[/TD]
[TD] Quantity[/TD]
[TD]UNIT[/TD]
[TD]P.O. SHIPMENT DATE[/TD]
[TD]Actual Ship DATE[/TD]
[TD]Remarks[/TD]
[TD]Designs/ Pantone From Customer[/TD]
[TD]P.P Samples Submition Date[/TD]
[TD]Days From Design To Sample Submission[/TD]
[TD]Approval Date[/TD]
[TD] Values[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD]95822[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[TD]ABC[/TD]
[TD]Humayun[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 24 Ctn 76X52[/TD]
[TD]Normal[/TD]
[TD] 676,830[/TD]
[TD]Set(s)[/TD]
[TD]20-Jan-14[/TD]
[TD]29-Jan-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 3,569.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]95825[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[TD]ABC[/TD]
[TD]Humayun[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 24 Ctn 76X52[/TD]
[TD]Over[/TD]
[TD] 201,520[/TD]
[TD]Set(s)[/TD]
[TD]20-Jan-14[/TD]
[TD]29-Jan-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,256.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]95828[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[TD]ABC[/TD]
[TD]Humayun[/TD]
[TD]Bed Sets[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 24 Ctn 76X52[/TD]
[TD]King[/TD]
[TD] 15,420[/TD]
[TD]Set(s)[/TD]
[TD]20-Jan-14[/TD]
[TD]29-Jan-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 3,498.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]95830[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[TD]ABC[/TD]
[TD]Bush[/TD]
[TD]Side Pillow[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 24 Ctn 76X52[/TD]
[TD]40x145[/TD]
[TD] 179,712[/TD]
[TD]Pc(s)[/TD]
[TD]20-Jan-14[/TD]
[TD]29-Jan-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,263.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]95831[/TD]
[TD]123[/TD]
[TD]31-Oct-13[/TD]
[TD]DEF[/TD]
[TD]Bush[/TD]
[TD]Pillow Pair[/TD]
[TD]CVC Renforce - 90 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]60% Cotton 40% Polyester[/TD]
[TD]100D X 24 Ctn 76X52[/TD]
[TD]40x80[/TD]
[TD] 436,032[/TD]
[TD]Pair(s)[/TD]
[TD]20-Jan-14[/TD]
[TD]29-Jan-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,236.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]40041541[/TD]
[TD]127[/TD]
[TD]27-Sep-13[/TD]
[TD]DEF[/TD]
[TD]Humayun[/TD]
[TD]Terry Towels[/TD]
[TD]CTN 400 Gsm PUNCH[/TD]
[TD]Reactive Dyed[/TD]
[TD]100% Cotton[/TD]
[TD][/TD]
[TD]Multiple[/TD]
[TD] 58,000[/TD]
[TD]Pc(s)[/TD]
[TD]18-Mar-14[/TD]
[TD][/TD]
[TD]Best Price Terry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,698.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]40043227[/TD]
[TD]127[/TD]
[TD]13-Feb-14[/TD]
[TD]DEF[/TD]
[TD]Michael[/TD]
[TD]Terry Towels[/TD]
[TD]CTN 400 Gsm PUNCH[/TD]
[TD]Reactive Dyed[/TD]
[TD]100% Cotton[/TD]
[TD][/TD]
[TD]Multiple[/TD]
[TD] 1,887[/TD]
[TD]Pc(s)[/TD]
[TD]25-Mar-14[/TD]
[TD][/TD]
[TD]Best Price Terry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,236.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]40042045[/TD]
[TD]129[/TD]
[TD]7-Nov-13[/TD]
[TD]DEF[/TD]
[TD]Michael[/TD]
[TD]Bed Sets[/TD]
[TD]CTN Renforce - 115 Gsm[/TD]
[TD]Pigment Printed Rotary[/TD]
[TD]50/50 Poly / Cotton[/TD]
[TD]30X30 / 76X68[/TD]
[TD]100x135 + 40x60[/TD]
[TD] 2,400[/TD]
[TD]Set(s)[/TD]
[TD]28-Jan-14[/TD]
[TD][/TD]
[TD]Lola / Pepito[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,236.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]12943122 A[/TD]
[TD]130[/TD]
[TD]11-Dec-13[/TD]
[TD]GHI[/TD]
[TD]Michael[/TD]
[TD]Pillow Pc[/TD]
[TD]CTN Renforce - 115 Gsm[/TD]
[TD]Reactive Dyed[/TD]
[TD]100% Cotton[/TD]
[TD]30X30 / 76X68[/TD]
[TD]Multiple[/TD]
[TD] 19,400[/TD]
[TD]Pc(s)[/TD]
[TD]18-Feb-14[/TD]
[TD][/TD]
[TD]Lindra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 1,236.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]12943122 B[/TD]
[TD]130[/TD]
[TD]11-Dec-13[/TD]
[TD]GHI[/TD]
[TD]Bush[/TD]
[TD]Duvet Cover[/TD]
[TD]CTN Renforce - 115 Gsm[/TD]
[TD]Reactive Dyed[/TD]
[TD]100% Cotton[/TD]
[TD]30X30 / 76X68[/TD]
[TD]Multiple[/TD]
[TD] 6,000[/TD]
[TD]Pc(s)[/TD]
[TD]18-Feb-14[/TD]
[TD][/TD]
[TD]Lindra[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 157.00[/TD]
[TD]Shipped[/TD]
[/TR]
[TR]
[TD]672571[/TD]
[TD]131[/TD]
[TD]21-Nov-13[/TD]
[TD]GHI[/TD]
[TD]Bush[/TD]
[TD]Fitted Sheet[/TD]
[TD]Interlock 135 GSM[/TD]
[TD]Reactive Dyed[/TD]
[TD]100% Polyester[/TD]
[TD]30'S Cotton[/TD]
[TD]Multiple[/TD]
[TD] 2,500[/TD]
[TD]Pc(s)[/TD]
[TD]20-Jan-14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] $ 19,876.00[/TD]
[TD]Shipped[/TD]
[/TR]
</tbody>[/TABLE]


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

I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
102
Peter_SSs
Peter_SSs
J
  • Solved
Search/Filter a Spreadsheet by User Entered Value
Replies
3
Views
90
jbennett01
J
S
  • Solved
How to filter and change text in Macro
Replies
2
Views
234
SCPbrito
S
S
  • Solved
Return error if "Filter" doesn't return the required entry
Replies
2
Views
118
skittlz
S
V
  • Question Question
autofilter
Replies
2
Views
269
vmjan02
V

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

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