S
Seeker
Is it possible pointing the selection to a reference cell instead of the drop
down list?
down list?
O
OssieMac
Can do it with code. How familiar are you with code. If given an example can
you adapt that to your needs? If you need extensive help then can you post a
bit more information:
The columns Id range of the autofilter data.
The column that you want to set the filter.
The cell address with the criteria.
How you want to start the code? (With a command button or on selection of
the cell with the criteria.)
you adapt that to your needs? If you need extensive help then can you post a
bit more information:
The columns Id range of the autofilter data.
The column that you want to set the filter.
The cell address with the criteria.
How you want to start the code? (With a command button or on selection of
the cell with the criteria.)
R
Ron de Bruin
S
Seeker
Ron,
If I didn’t miss anything, I only located a pre-defined value in your
example code, what I am looking for is something like Criteria1:="sheet1.A1".
However, your lists are treasure to me, hope your don’t mind, I have
bookmarked it for future reference. Thanks again for your help.
Regards
If I didn’t miss anything, I only located a pre-defined value in your
example code, what I am looking for is something like Criteria1:="sheet1.A1".
However, your lists are treasure to me, hope your don’t mind, I have
bookmarked it for future reference. Thanks again for your help.
Regards
S
Seeker
Ossie,
My knowledge in coding is from the “macro recorder†only, am able to twist
the recorded code a bit to fit my project. My data range is A:X in sheet
named “Recordsâ€. I intended to have 5 reference cells with drop down list
(drop down list is done already) in sheet named “Oda Inputâ€, the listing are
choices including (All) for end-users’ selection. After end-users selected
filter criteria within these five cells, they need to press the command
button to activate filter macro and copy result of filtering to sheet named
“Print†for screen viewing and print it out. I tried advanced filter but I
cannot pre-define the criteria range to select all data.
If you don’t mind, would you please also check for me is there anything I
can do to improve the macro running time? Although this macro produces my
expected result, it halts for minutes and screen showing “filter†keeps on
flashing before macro runs to the end.
Sheets("Records").Select
Range("A1:X65536").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("I1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlStroke,
DataOption1:= _
xlSortNormal, DataOption2:=xlSortNormal
Selection.AutoFilter Field:=3, Criteria1:="<>"
Thanks in advance.
Best Regards
My knowledge in coding is from the “macro recorder†only, am able to twist
the recorded code a bit to fit my project. My data range is A:X in sheet
named “Recordsâ€. I intended to have 5 reference cells with drop down list
(drop down list is done already) in sheet named “Oda Inputâ€, the listing are
choices including (All) for end-users’ selection. After end-users selected
filter criteria within these five cells, they need to press the command
button to activate filter macro and copy result of filtering to sheet named
“Print†for screen viewing and print it out. I tried advanced filter but I
cannot pre-define the criteria range to select all data.
If you don’t mind, would you please also check for me is there anything I
can do to improve the macro running time? Although this macro produces my
expected result, it halts for minutes and screen showing “filter†keeps on
flashing before macro runs to the end.
Sheets("Records").Select
Range("A1:X65536").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("I1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlStroke,
DataOption1:= _
xlSortNormal, DataOption2:=xlSortNormal
Selection.AutoFilter Field:=3, Criteria1:="<>"
Thanks in advance.
Best Regards
R
Ron de Bruin
You see this in the first code example
http://www.rondebruin.nl/copy5_1.htm
'This will use the cell value from A2 as criteria
'My_Range.AutoFilter Field:=1, Criteria1:="=" & Range("A2").Value
Replace this part with the above
'Filter and set the filter field and the filter criteria :
'This example filter on the first column in the range (change the field if needed)
'In this case the range starts in A so Field 1 is column A, 2 = column B, ......
'Use "<>Netherlands" as criteria if you want the opposite
My_Range.AutoFilter Field:=1, Criteria1:="=Netherlands"
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
http://www.rondebruin.nl/copy5_1.htm
'This will use the cell value from A2 as criteria
'My_Range.AutoFilter Field:=1, Criteria1:="=" & Range("A2").Value
Replace this part with the above
'Filter and set the filter field and the filter criteria :
'This example filter on the first column in the range (change the field if needed)
'In this case the range starts in A so Field 1 is column A, 2 = column B, ......
'Use "<>Netherlands" as criteria if you want the opposite
My_Range.AutoFilter Field:=1, Criteria1:="=Netherlands"
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
O
OssieMac
Hello again,
Ron's answers do provide all the info but as it appears that you are
struggling with the code I will attempt to write some code for you that
should do the trick. However, in the mean time you might like to answer my
question below so that I can answer your specific question "please also check
for me is there anything I can do to improve the macro running time?"
Range("A1:X65536").Select
The above line is possably the biggest problem. I suggest that you are
sorting to the last row because you have data with a variable number of rows.
If you can nominate a column that will always have data in all rows then we
can sort based on the actual data. (There is a command UsedRange but I find
it unreliable if there is any formatting in cells outside the actual data
range.)
Ron's answers do provide all the info but as it appears that you are
struggling with the code I will attempt to write some code for you that
should do the trick. However, in the mean time you might like to answer my
question below so that I can answer your specific question "please also check
for me is there anything I can do to improve the macro running time?"
Range("A1:X65536").Select
The above line is possably the biggest problem. I suggest that you are
sorting to the last row because you have data with a variable number of rows.
If you can nominate a column that will always have data in all rows then we
can sort based on the actual data. (There is a command UsedRange but I find
it unreliable if there is any formatting in cells outside the actual data
range.)
S
Seeker
Ron,
Thanks again and sorry about my oversight on that line. I will try again.
Million thanks again.
Thanks again and sorry about my oversight on that line. I will try again.
Million thanks again.
S
Seeker
Hi Ossie,
Once again thanks for your generosity in lending me your big hand in the
coding; I also will try myself to embed this macro for your emendation.
Regarding Range("A1:X65536").Select, I didn’t though of range problem as the
sheet “Records†was intended to keep all records and new added item will be
appended to next empty line, so I just to play safe and selected all rows. Is
it workable in replacing the code by following?
Range("A2:X").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("I1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlStroke,
DataOption1:= _
xlSortNormal, DataOption2:=xlSortNormal
Range("A1:X1").Select
AutoFilterMode = True
Selection.AutoFilter Field:=3, Criteria1:="<>"
Once again thanks for your generosity in lending me your big hand in the
coding; I also will try myself to embed this macro for your emendation.
Regarding Range("A1:X65536").Select, I didn’t though of range problem as the
sheet “Records†was intended to keep all records and new added item will be
appended to next empty line, so I just to play safe and selected all rows. Is
it workable in replacing the code by following?
Range("A2:X").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("I1") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, SortMethod:=xlStroke,
DataOption1:= _
xlSortNormal, DataOption2:=xlSortNormal
Range("A1:X1").Select
AutoFilterMode = True
Selection.AutoFilter Field:=3, Criteria1:="<>"
S
Seeker
Sorry Ossie,
After testing the code, it should be as follow or your better way.
Sheets("Records").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
After testing the code, it should be as follow or your better way.
Sheets("Records").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
S
Seeker
Hi Ron,
Sorry to bother you again. I am try to use your code to produce the output.
Since I am not familiar with coding (corelation between the Dim, String
etc.), so I adopt the macro recorder's method and change your code
My_Range.AutoFilter Field:=1, Criteria1:=
to
Selection.AutoFilter Field:=5, Criteria1:="=" & Sheets("Oda
Input").Range("J19").Value
However, I found the filter can point to the value in cell J19 but it
doesn't (click) to make it effective, thus all data are hide.
Is there any additional action need to be taken to activate this filtering
pleasse?
Rgds
Sorry to bother you again. I am try to use your code to produce the output.
Since I am not familiar with coding (corelation between the Dim, String
etc.), so I adopt the macro recorder's method and change your code
My_Range.AutoFilter Field:=1, Criteria1:=
to
Selection.AutoFilter Field:=5, Criteria1:="=" & Sheets("Oda
Input").Range("J19").Value
However, I found the filter can point to the value in cell J19 but it
doesn't (click) to make it effective, thus all data are hide.
Is there any additional action need to be taken to activate this filtering
pleasse?
Rgds
O
OssieMac
Hi Seeker,
Your previous example was incorrect in Range("A2:X").Select. It should be
A2:X2 not A2:X. Otherwise it should work. However, I like your next example
better because it is dynamic for the number of columns and the number of rows.
I won’t try to confuse you with substitute code that does not require the
selection of the ranges. However, you will see that the code below for your
AutoFilter does not select any worksheets or ranges until finished and will
run entirely from the worksheet with the command button until the end of the
code when it selects the Print sheet for the user to view.
Your AutoFilter code I am doubtful about.
AutoFilterMode = True 'Returns an error
Range("A1").AutoFilter 'Toggles AutoFilter on and off.
However, the following will turn it on if not on and does not affect it if
it is on.
Sheets("Records").Range("A1").AutoFilter Field:=1
Try out the following code for the AutoFilter part of your project.
You will need to edit the cell adresses for your criteria to match your
cells for each of the 5 criteria. Also edit the field numbers where the Field
criteria is set. See my comments in the code.
You possibly know this but just in case a space and underscore at the end of
a line is a line break in an otherwise single line of code.
Sub ProcessPrintReport()
Dim crit1 As Variant 'Holds criteria cell values
Dim crit2 As Variant
Dim crit3 As Variant
Dim crit4 As Variant
Dim crit5 As Variant
'Test that all criteria cells have been populated.
'Assign the criteria cell values to variables.
'Edit the criteria cell addresses to suit your project.
'Can leave crit1 to crit5 variables as is.
With Sheets("Oda Input")
If .Range("A12") = "" Then
GoTo MsgeToUser
Else
crit1 = .Range("A12")
End If
If .Range("B12") = "" Then
GoTo MsgeToUser
Else
crit2 = .Range("B12")
End If
If .Range("C12") = "" Then
GoTo MsgeToUser
Else
crit3 = .Range("C12")
End If
If .Range("D12") = "" Then
GoTo MsgeToUser
Else
crit4 = .Range("D12")
End If
If .Range("E12") = "" Then
GoTo MsgeToUser
Else
crit5 = .Range("E12")
End If
End With
'If no blank criteria then skip the error message.
GoTo PastErrorMsge
MsgeToUser:
MsgBox "All criteria cells must be populated." _
& vbCrLf & "Processing terminated."
Exit Sub
PastErrorMsge:
With Sheets("Records")
'Ensure that AutoFilter is turned on.
'This avoids error problems if not turned on.
.Range("A1").AutoFilter field:=1
'Remove all existing filters (if any).
If .FilterMode Then 'FilterMode is true if a filter is actually set.
.ShowAllData
End If
End With
With Sheets("Records").AutoFilter.Range
'Counting from left of AutoFiltered range
'edit the Field number to suit your fields
'no need to change crit1 to crit5 variables.
If crit1 = "All" Then
.AutoFilter field:=1
Else
.AutoFilter field:=1, Criteria1:=crit1
End If
If crit2 = "All" Then
.AutoFilter field:=2
Else
.AutoFilter field:=2, Criteria1:=crit2
End If
If crit3 = "All" Then
.AutoFilter field:=3
Else
.AutoFilter field:=3, Criteria1:=crit3
End If
If crit4 = "All" Then
.AutoFilter field:=4
Else
.AutoFilter field:=4, Criteria1:=crit4
End If
If crit5 = "All" Then
.AutoFilter field:=5
Else
.AutoFilter field:=5, Criteria1:=crit5
End If
End With
'Clear any existing data from the Print Sheet
Sheets("Print").Cells.Clear
'Copy the Filtered data to Print Worksheet
Sheets("Records").AutoFilter.Range.Copy _
Destination:=Sheets("Print").Range("A1")
'Following is optional code.
'it makes headers bold, Autofits columns
'and then selects the Print worksheet.
With Sheets("Print")
.Range(.Cells(1, 1), _
.Cells(1, Columns.Count) _
.End(xlToLeft)).Font.Bold = True
.UsedRange.Columns.AutoFit
.Select
End With
'Ensure cell A1 is top left cell of window
'so that data is visible.
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub
Your previous example was incorrect in Range("A2:X").Select. It should be
A2:X2 not A2:X. Otherwise it should work. However, I like your next example
better because it is dynamic for the number of columns and the number of rows.
I won’t try to confuse you with substitute code that does not require the
selection of the ranges. However, you will see that the code below for your
AutoFilter does not select any worksheets or ranges until finished and will
run entirely from the worksheet with the command button until the end of the
code when it selects the Print sheet for the user to view.
Your AutoFilter code I am doubtful about.
AutoFilterMode = True 'Returns an error
Range("A1").AutoFilter 'Toggles AutoFilter on and off.
However, the following will turn it on if not on and does not affect it if
it is on.
Sheets("Records").Range("A1").AutoFilter Field:=1
Try out the following code for the AutoFilter part of your project.
You will need to edit the cell adresses for your criteria to match your
cells for each of the 5 criteria. Also edit the field numbers where the Field
criteria is set. See my comments in the code.
You possibly know this but just in case a space and underscore at the end of
a line is a line break in an otherwise single line of code.
Sub ProcessPrintReport()
Dim crit1 As Variant 'Holds criteria cell values
Dim crit2 As Variant
Dim crit3 As Variant
Dim crit4 As Variant
Dim crit5 As Variant
'Test that all criteria cells have been populated.
'Assign the criteria cell values to variables.
'Edit the criteria cell addresses to suit your project.
'Can leave crit1 to crit5 variables as is.
With Sheets("Oda Input")
If .Range("A12") = "" Then
GoTo MsgeToUser
Else
crit1 = .Range("A12")
End If
If .Range("B12") = "" Then
GoTo MsgeToUser
Else
crit2 = .Range("B12")
End If
If .Range("C12") = "" Then
GoTo MsgeToUser
Else
crit3 = .Range("C12")
End If
If .Range("D12") = "" Then
GoTo MsgeToUser
Else
crit4 = .Range("D12")
End If
If .Range("E12") = "" Then
GoTo MsgeToUser
Else
crit5 = .Range("E12")
End If
End With
'If no blank criteria then skip the error message.
GoTo PastErrorMsge
MsgeToUser:
MsgBox "All criteria cells must be populated." _
& vbCrLf & "Processing terminated."
Exit Sub
PastErrorMsge:
With Sheets("Records")
'Ensure that AutoFilter is turned on.
'This avoids error problems if not turned on.
.Range("A1").AutoFilter field:=1
'Remove all existing filters (if any).
If .FilterMode Then 'FilterMode is true if a filter is actually set.
.ShowAllData
End If
End With
With Sheets("Records").AutoFilter.Range
'Counting from left of AutoFiltered range
'edit the Field number to suit your fields
'no need to change crit1 to crit5 variables.
If crit1 = "All" Then
.AutoFilter field:=1
Else
.AutoFilter field:=1, Criteria1:=crit1
End If
If crit2 = "All" Then
.AutoFilter field:=2
Else
.AutoFilter field:=2, Criteria1:=crit2
End If
If crit3 = "All" Then
.AutoFilter field:=3
Else
.AutoFilter field:=3, Criteria1:=crit3
End If
If crit4 = "All" Then
.AutoFilter field:=4
Else
.AutoFilter field:=4, Criteria1:=crit4
End If
If crit5 = "All" Then
.AutoFilter field:=5
Else
.AutoFilter field:=5, Criteria1:=crit5
End If
End With
'Clear any existing data from the Print Sheet
Sheets("Print").Cells.Clear
'Copy the Filtered data to Print Worksheet
Sheets("Records").AutoFilter.Range.Copy _
Destination:=Sheets("Print").Range("A1")
'Following is optional code.
'it makes headers bold, Autofits columns
'and then selects the Print worksheet.
With Sheets("Print")
.Range(.Cells(1, 1), _
.Cells(1, Columns.Count) _
.End(xlToLeft)).Font.Bold = True
.UsedRange.Columns.AutoFit
.Select
End With
'Ensure cell A1 is top left cell of window
'so that data is visible.
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub
S
Seeker
Ossie,
Thanks again for your prompt reply. I changed my code to following according
to your advice.
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="<>"
Regarding the code you made for me, I have difficulty in getting an expected
result as the filter doesn't perform properly. When I test run the code,
although the pull down list in AutoFilter stays right at the “Variant†value,
it still hides all rows instead of showing relative rows, just wonder should
it need another function code to activate it? I used following code to test
the filter.
Sub ProcessPrintReport()
Dim crit1 As Variant
Sheets("Oda Input").Select
crit1 = Range("J18")
Sheets("Records").Select
Range("A1").Select
Selection.AutoFilter field:=5, Criteria1:=crit1
End Sub
Thanks again for your prompt reply. I changed my code to following according
to your advice.
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="<>"
Regarding the code you made for me, I have difficulty in getting an expected
result as the filter doesn't perform properly. When I test run the code,
although the pull down list in AutoFilter stays right at the “Variant†value,
it still hides all rows instead of showing relative rows, just wonder should
it need another function code to activate it? I used following code to test
the filter.
Sub ProcessPrintReport()
Dim crit1 As Variant
Sheets("Oda Input").Select
crit1 = Range("J18")
Sheets("Records").Select
Range("A1").Select
Selection.AutoFilter field:=5, Criteria1:=crit1
End Sub
O
OssieMac
Your quote "I changed my code to following according to your advice.
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="<>"
I don't believe that I advised you to do that. In fact unless you have a row
of data that has <> in the cell then I don't think it will work.
However, a few more questions regarding the sample code problem.
What version of Excel are you using?
What is the cell address of each of the 5 dropdowns for selecting the
criteria.
What Field number does each of the cell addresses pertain to for the
Autofilter.
Can you post samples of the data that you have in the 5 dropdown lists.
(Perhaps the data type is a problem.)
Range("A1").Select
Selection.AutoFilter Field:=3, Criteria1:="<>"
I don't believe that I advised you to do that. In fact unless you have a row
of data that has <> in the cell then I don't think it will work.
However, a few more questions regarding the sample code problem.
What version of Excel are you using?
What is the cell address of each of the 5 dropdowns for selecting the
criteria.
What Field number does each of the cell addresses pertain to for the
Autofilter.
Can you post samples of the data that you have in the 5 dropdown lists.
(Perhaps the data type is a problem.)
S
Seeker
Ossie,
Million thanks again for your time.
The “<>†I used in my code is means for (NonBlanks) which was provided by
the recorder. What I mean is your advice in shortening the code, anyway, the
code works now, thanks for the checking.
I am using Excel 2003.
Data in sheet “Records†were stored by “Selection.PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=Falseâ€
Reference cells and cell addresses pertain to for the Autofilter to be adopt
in your sample code as below:
The dropdown lists are in sheet “Oda Input†and AutoFilter is in sheet
“Recordsâ€,
crit1 = J19 samples: 000602 or 100312 or AAAA (hundreds of records) pertain
to AutoFilter field:=5 (data were input in 3 digit, 6digit or 4 alphabet,
those numbers with 3 digits were formatted as “000000â€, in the source list or
dropdown list of this validation even copied to sheet “Records†always
showing 6 digits in cell but 3 digits in “Formula barâ€)
crit2 = J20 samples: BBBBBB (6 alphabet, hundreds of records) pertain to
AutoFilter field:=2
crit3 = J22 samples: ADD or CANCEL or EXECUTE or MODIFY (only 4 choices)
pertain to AutoFilter field:=21
crit4 = J35 samples: activate or pending or “ “(empty) (only 3 choices)
pertain to AutoFilter field:=3
crit5 = J19 samples: input of date & time by user (start date/time)
crit6 = J19 samples: input of date & time by user (end date/time)
crit5 and crit6 are pertain to AutoFilter field:=16 (data in column 16 will
be selected if fall between these two date just like Ron’s example AutoFilter
Field:=4, Criteria1:=">= "crit6, Operator:=xlAnd, Criteria2:="<="crit5)
Rgds
Million thanks again for your time.
The “<>†I used in my code is means for (NonBlanks) which was provided by
the recorder. What I mean is your advice in shortening the code, anyway, the
code works now, thanks for the checking.
I am using Excel 2003.
Data in sheet “Records†were stored by “Selection.PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=Falseâ€
Reference cells and cell addresses pertain to for the Autofilter to be adopt
in your sample code as below:
The dropdown lists are in sheet “Oda Input†and AutoFilter is in sheet
“Recordsâ€,
crit1 = J19 samples: 000602 or 100312 or AAAA (hundreds of records) pertain
to AutoFilter field:=5 (data were input in 3 digit, 6digit or 4 alphabet,
those numbers with 3 digits were formatted as “000000â€, in the source list or
dropdown list of this validation even copied to sheet “Records†always
showing 6 digits in cell but 3 digits in “Formula barâ€)
crit2 = J20 samples: BBBBBB (6 alphabet, hundreds of records) pertain to
AutoFilter field:=2
crit3 = J22 samples: ADD or CANCEL or EXECUTE or MODIFY (only 4 choices)
pertain to AutoFilter field:=21
crit4 = J35 samples: activate or pending or “ “(empty) (only 3 choices)
pertain to AutoFilter field:=3
crit5 = J19 samples: input of date & time by user (start date/time)
crit6 = J19 samples: input of date & time by user (end date/time)
crit5 and crit6 are pertain to AutoFilter field:=16 (data in column 16 will
be selected if fall between these two date just like Ron’s example AutoFilter
Field:=4, Criteria1:=">= "crit6, Operator:=xlAnd, Criteria2:="<="crit5)
Rgds
O
OssieMac
Hi again,
We are obviously in a very different time zone and hense the delay in some
of my replies. It is currently 7:30am here. I have to go out for a couple of
hours and then I will get back on this to provide the answer as soon as
possible. I think that I understand now what you are trying to achieve.
However, I would like you to do one more thing for me and then perhaps we can
be sure of getting it correct. (I should have thought of this with my last
post because it is the best way to tell me exactly how you want the filters
set).
In your Records sheet ensure that all filters are set to All. (Turn
AutoFilter off then on again).
Turn on the Macro recorder.
Set the required filters manually.
Turn off the Macro recorder.
Post the code exactly as recorded.
We are obviously in a very different time zone and hense the delay in some
of my replies. It is currently 7:30am here. I have to go out for a couple of
hours and then I will get back on this to provide the answer as soon as
possible. I think that I understand now what you are trying to achieve.
However, I would like you to do one more thing for me and then perhaps we can
be sure of getting it correct. (I should have thought of this with my last
post because it is the best way to tell me exactly how you want the filters
set).
In your Records sheet ensure that all filters are set to All. (Turn
AutoFilter off then on again).
Turn on the Macro recorder.
Set the required filters manually.
Turn off the Macro recorder.
Post the code exactly as recorded.
S
Seeker
Tks Again Ossie,
Here is the code I recorded under sheet “Recordsâ€, .
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="000600" (value of sheet
“Oda Input†cell J19)
Selection.AutoFilter Field:=2 'Criteria1:="AAAAAA" (value of sheet “Oda
Input†cell J20)
Selection.AutoFilter Field:=21, Criteria1:="ADD" (value of sheet “Oda
Input†cell J22)
Selection.AutoFilter Field:=3, Criteria1:="activate" (value of sheet
“Oda Input†cell J35)
Selection.AutoFilter Field:=16, Criteria1:="mm dd hh:mm" (output of a
formula that value between sheet “Oda Input†cell J36 & J38)
Here is the code I recorded under sheet “Recordsâ€, .
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="000600" (value of sheet
“Oda Input†cell J19)
Selection.AutoFilter Field:=2 'Criteria1:="AAAAAA" (value of sheet “Oda
Input†cell J20)
Selection.AutoFilter Field:=21, Criteria1:="ADD" (value of sheet “Oda
Input†cell J22)
Selection.AutoFilter Field:=3, Criteria1:="activate" (value of sheet
“Oda Input†cell J35)
Selection.AutoFilter Field:=16, Criteria1:="mm dd hh:mm" (output of a
formula that value between sheet “Oda Input†cell J36 & J38)
O
OssieMac
Hi Seeker,
Sorry it has taken so long to get back to you but I have had a lot of
problems with the date code. I am in a d/m/y date format region and everytime
I set the Autofilter with the code it would not display until I clicked the
drop down and closed it manually. I believe you also had that problem. I
finally found the following post by Dave Petersen on 20th Feb 2009.
Start of Dave Petersen’s Post ********************************************
This is from "Excel 2002 VBA Programmer's Reference"
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg
http://www.oaltd.co.uk:80/ExcelProgRef/Ch22/ProgRefCh22.htm
Search for "Range.AutoFilter" and you'll see this note:
Range.AutoFilter
The AutoFilter method of a Range object is a very curious beast. We are
forced
to pass it strings for its filter criteria and hence must be aware of its
string
handling behaviour. The criteria string consists of an operator (=, >, <, >=
etc.) followed by a value.
If no operator is specified, the "=" operator is assumed. The key issue is
that
when using the "=" operator, AutoFilter performs a textual match, while using
any other operator results in a match by value. This gives us problems when
trying to locate exact matches for dates and numbers.
If we use "=", Excel matches on the text that is displayed in the cell, i.e.
the
formatted number. As the text displayed in a cell will change with different
regional settings and Windows language version, it is impossible for us to
create a criteria string that will locate an exact match in all locales.
There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string
according
to US formats. Hence, a search criterion of ">=02/01/2001" will find all
dates
on or after 1st Feb, 2001, in all locales.
We can use this to match an exact date by using two AutoFilter criteria. The
following code will give an exact match on 1st Feb, 2001 and will work in any
locale:
Range("A1
200").AutoFilter 2, ">=02/01/2001", xlAnd, "<=02/01/2001"
So in your case:
ActiveSheet.Range("$A$1:$S$277").AutoFilter Field:=1, _
Criteria1:=">=" & format(StartDate,"mm/dd/yyyy"), _
Operator:=xlAnd, Criteria2:="<=" & format(EndDate,"mm/dd/yyyy")
End of Dave Petersen’s post *******************************************
Anyway I have come up with the following code. If not correct then confirm
that all the following are correct.
My understanding is that all of the dropdowns have an All option. Therefore
the code sets the filter to All if that is selected. Code is a bit more
complex for the dates in this area but I think I have it correct.
Your dates have both date and time displayed. (If not then modify the
formats in the Criteria setting but ensure you use the American date format
irrespective of your regional date format)
Your dates in the AutoFiltered list, DropDown list and the DropDown cell
should all be formatted the same.
If still not working and you would like me to have a look at your workbook
then if you want to get a hotmail (or some other email provider) address that
you can abandon later if you want and post it then I will reply and you may
send me the workbook.
When posting an email address don’t just type out the address in the post.
Post it something like myname at hotmail dot com and then say replace myname
with (whatever name you use for the address.) This helps to prevent automated
programs searching the internet to find email addresses in the text because
it does not look and email address.
Sub ProcessPrintReport()
Dim crit1 As Variant 'Holds criteria cell values
Dim crit2 As Variant
Dim crit3 As Variant
Dim crit4 As Variant
Dim crit5 As Variant
Dim crit6 As Variant
'Test that all criteria cells have been populated.
'Assign the criteria cell values to variables.
'Edit the criteria cell addresses to suit your project.
'Can leave crit1 to crit5 variables as is.
With Sheets("Oda Input")
If .Range("J19") = "" Then
GoTo MsgeToUser
Else
crit1 = Format(.Range("J19"), "000000")
End If
If .Range("J20") = "" Then
GoTo MsgeToUser
Else
crit2 = .Range("J20")
End If
If .Range("J22") = "" Then
GoTo MsgeToUser
Else
crit3 = .Range("J22")
End If
If .Range("J35") = "" Then
GoTo MsgeToUser
Else
crit4 = .Range("J35")
End If
If .Range("J36") = "" Then
GoTo MsgeToUser
Else
crit5 = .Range("J36")
End If
If .Range("J38") = "" Then
GoTo MsgeToUser
Else
crit6 = .Range("J38")
End If
End With
'If no blank criteria then skip the error message.
GoTo PastErrorMsge
MsgeToUser:
MsgBox "All criteria cells must be populated." _
& vbCrLf & "Processing terminated."
Exit Sub
PastErrorMsge:
With Sheets("Records")
'Ensure that AutoFilter is turned on.
'This avoids error problems if not turned on.
.Range("A1").AutoFilter Field:=1
'Remove all existing filters (if any).
If .FilterMode Then
.ShowAllData
End If
End With
With Sheets("Records").AutoFilter.Range
'Counting from left of AutoFiltered range
'edit the Field number to suit your fields
'no need to change crit1 to crit5 variables.
If crit1 = "All" Then
.AutoFilter Field:=5
Else
.AutoFilter Field:=5, Criteria1:="=" & crit1
End If
If crit2 = "All" Then
.AutoFilter Field:=2
Else
.AutoFilter Field:=2, Criteria1:="=" & crit2
End If
If crit3 = "All" Then
.AutoFilter Field:=21
Else
.AutoFilter Field:=21, Criteria1:="=" & crit3
End If
If crit4 = "All" Then
.AutoFilter Field:=3
Else
.AutoFilter Field:=3, Criteria1:="=" & crit4
End If
If crit5 = "All" Then
If crit6 = "All" Then
.AutoFilter Field:=16 'Both All - No filter
Else
'Filter on crit6 only
.AutoFilter Field:=16, Criteria1:="<=" _
& Format(crit6, "mm/dd/yyyy hh:mm")
End If
Else
'crit5 has date
If crit6 = "All" Then 'Filter on crit5 only
.AutoFilter Field:=16, Criteria1:=">=" _
& Format(crit5, "mm/dd/yyyy hh:mm")
Else
'Both crit5 & crit6 have dates
.AutoFilter Field:=16, Criteria1:=">=" & _
Format(crit5, "mm/dd/yyyy hh:mm"), Operator:= _
xlAnd, Criteria2:="<=" & _
Format(crit6, "mm/dd/yyyy hh:mm")
End If
End If
End With
'Clear any existing data from the Print Sheet
Sheets("Print").Cells.Clear
'Copy the Filtered data to Print Worksheet
Sheets("Records").AutoFilter.Range.Copy _
Destination:=Sheets("Print").Range("A1")
'Following is optional code.
'it makes headers bold, Autofits columns
'and then selects the Print worksheet.
With Sheets("Print")
.Range(.Cells(1, 1), _
.Cells(1, Columns.Count) _
.End(xlToLeft)).Font.Bold = True
.UsedRange.Columns.AutoFit
.Select
End With
'Ensure cell A1 is top left cell of window
'so that data is visible.
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub
Sorry it has taken so long to get back to you but I have had a lot of
problems with the date code. I am in a d/m/y date format region and everytime
I set the Autofilter with the code it would not display until I clicked the
drop down and closed it manually. I believe you also had that problem. I
finally found the following post by Dave Petersen on 20th Feb 2009.
Start of Dave Petersen’s Post ********************************************
This is from "Excel 2002 VBA Programmer's Reference"
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg
http://www.oaltd.co.uk:80/ExcelProgRef/Ch22/ProgRefCh22.htm
Search for "Range.AutoFilter" and you'll see this note:
Range.AutoFilter
The AutoFilter method of a Range object is a very curious beast. We are
forced
to pass it strings for its filter criteria and hence must be aware of its
string
handling behaviour. The criteria string consists of an operator (=, >, <, >=
etc.) followed by a value.
If no operator is specified, the "=" operator is assumed. The key issue is
that
when using the "=" operator, AutoFilter performs a textual match, while using
any other operator results in a match by value. This gives us problems when
trying to locate exact matches for dates and numbers.
If we use "=", Excel matches on the text that is displayed in the cell, i.e.
the
formatted number. As the text displayed in a cell will change with different
regional settings and Windows language version, it is impossible for us to
create a criteria string that will locate an exact match in all locales.
There is a workaround for this problem. When using any of the other filter
criteria, Excel plays by the rules and interprets the criteria string
according
to US formats. Hence, a search criterion of ">=02/01/2001" will find all
dates
on or after 1st Feb, 2001, in all locales.
We can use this to match an exact date by using two AutoFilter criteria. The
following code will give an exact match on 1st Feb, 2001 and will work in any
locale:
Range("A1
200").AutoFilter 2, ">=02/01/2001", xlAnd, "<=02/01/2001"So in your case:
ActiveSheet.Range("$A$1:$S$277").AutoFilter Field:=1, _
Criteria1:=">=" & format(StartDate,"mm/dd/yyyy"), _
Operator:=xlAnd, Criteria2:="<=" & format(EndDate,"mm/dd/yyyy")
End of Dave Petersen’s post *******************************************
Anyway I have come up with the following code. If not correct then confirm
that all the following are correct.
My understanding is that all of the dropdowns have an All option. Therefore
the code sets the filter to All if that is selected. Code is a bit more
complex for the dates in this area but I think I have it correct.
Your dates have both date and time displayed. (If not then modify the
formats in the Criteria setting but ensure you use the American date format
irrespective of your regional date format)
Your dates in the AutoFiltered list, DropDown list and the DropDown cell
should all be formatted the same.
If still not working and you would like me to have a look at your workbook
then if you want to get a hotmail (or some other email provider) address that
you can abandon later if you want and post it then I will reply and you may
send me the workbook.
When posting an email address don’t just type out the address in the post.
Post it something like myname at hotmail dot com and then say replace myname
with (whatever name you use for the address.) This helps to prevent automated
programs searching the internet to find email addresses in the text because
it does not look and email address.
Sub ProcessPrintReport()
Dim crit1 As Variant 'Holds criteria cell values
Dim crit2 As Variant
Dim crit3 As Variant
Dim crit4 As Variant
Dim crit5 As Variant
Dim crit6 As Variant
'Test that all criteria cells have been populated.
'Assign the criteria cell values to variables.
'Edit the criteria cell addresses to suit your project.
'Can leave crit1 to crit5 variables as is.
With Sheets("Oda Input")
If .Range("J19") = "" Then
GoTo MsgeToUser
Else
crit1 = Format(.Range("J19"), "000000")
End If
If .Range("J20") = "" Then
GoTo MsgeToUser
Else
crit2 = .Range("J20")
End If
If .Range("J22") = "" Then
GoTo MsgeToUser
Else
crit3 = .Range("J22")
End If
If .Range("J35") = "" Then
GoTo MsgeToUser
Else
crit4 = .Range("J35")
End If
If .Range("J36") = "" Then
GoTo MsgeToUser
Else
crit5 = .Range("J36")
End If
If .Range("J38") = "" Then
GoTo MsgeToUser
Else
crit6 = .Range("J38")
End If
End With
'If no blank criteria then skip the error message.
GoTo PastErrorMsge
MsgeToUser:
MsgBox "All criteria cells must be populated." _
& vbCrLf & "Processing terminated."
Exit Sub
PastErrorMsge:
With Sheets("Records")
'Ensure that AutoFilter is turned on.
'This avoids error problems if not turned on.
.Range("A1").AutoFilter Field:=1
'Remove all existing filters (if any).
If .FilterMode Then
.ShowAllData
End If
End With
With Sheets("Records").AutoFilter.Range
'Counting from left of AutoFiltered range
'edit the Field number to suit your fields
'no need to change crit1 to crit5 variables.
If crit1 = "All" Then
.AutoFilter Field:=5
Else
.AutoFilter Field:=5, Criteria1:="=" & crit1
End If
If crit2 = "All" Then
.AutoFilter Field:=2
Else
.AutoFilter Field:=2, Criteria1:="=" & crit2
End If
If crit3 = "All" Then
.AutoFilter Field:=21
Else
.AutoFilter Field:=21, Criteria1:="=" & crit3
End If
If crit4 = "All" Then
.AutoFilter Field:=3
Else
.AutoFilter Field:=3, Criteria1:="=" & crit4
End If
If crit5 = "All" Then
If crit6 = "All" Then
.AutoFilter Field:=16 'Both All - No filter
Else
'Filter on crit6 only
.AutoFilter Field:=16, Criteria1:="<=" _
& Format(crit6, "mm/dd/yyyy hh:mm")
End If
Else
'crit5 has date
If crit6 = "All" Then 'Filter on crit5 only
.AutoFilter Field:=16, Criteria1:=">=" _
& Format(crit5, "mm/dd/yyyy hh:mm")
Else
'Both crit5 & crit6 have dates
.AutoFilter Field:=16, Criteria1:=">=" & _
Format(crit5, "mm/dd/yyyy hh:mm"), Operator:= _
xlAnd, Criteria2:="<=" & _
Format(crit6, "mm/dd/yyyy hh:mm")
End If
End If
End With
'Clear any existing data from the Print Sheet
Sheets("Print").Cells.Clear
'Copy the Filtered data to Print Worksheet
Sheets("Records").AutoFilter.Range.Copy _
Destination:=Sheets("Print").Range("A1")
'Following is optional code.
'it makes headers bold, Autofits columns
'and then selects the Print worksheet.
With Sheets("Print")
.Range(.Cells(1, 1), _
.Cells(1, Columns.Count) _
.End(xlToLeft)).Font.Bold = True
.UsedRange.Columns.AutoFit
.Select
End With
'Ensure cell A1 is top left cell of window
'so that data is visible.
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
End Sub
S
Seeker
Ossie,
You are so kind and spent so long time in solving the problem in my project.
I will let you know the result latest the coming weekend. Thanks again.
Rgds
You are so kind and spent so long time in solving the problem in my project.
I will let you know the result latest the coming weekend. Thanks again.
Rgds
S
Seeker
Hi OssieMac,
Your code is brilliant, million thanks again for your help in sorting out my
problems and now my project is done.
Cods need to fine tune -- crit5 & crit6 need to be formatted while as
variants instead of formatting values while running filter:
Dim crit5 As Variant
Dim crit6 As Variant
With Sheets("Oda Input")
crit5 = .Range(Format("J36","mm/dd/yyyy hh:mm"))
crit6 = .Range(Format("J38","mm/dd/yyyy hh:mm"))
End With
With Sheets("Records").AutoFilter.Range
If crit5 = "" Then
If crit6 = "" Then
.AutoFilter Field:=16 'Both All - No filter
Else
.AutoFilter Field:=16, Criteria1:="<=" & crit6
End If
Else
If crit6 = "(All)" Then 'Filter on crit5 only
.AutoFilter Field:=16, Criteria1:=">=" & crit5
Else
.AutoFilter Field:=16, Criteria1:=">=" & crit5, Operator:=xlAnd,
Criteria2:="<=" & crit6
End If
End If
End With
Rgds
Your code is brilliant, million thanks again for your help in sorting out my
problems and now my project is done.
Cods need to fine tune -- crit5 & crit6 need to be formatted while as
variants instead of formatting values while running filter:
Dim crit5 As Variant
Dim crit6 As Variant
With Sheets("Oda Input")
crit5 = .Range(Format("J36","mm/dd/yyyy hh:mm"))
crit6 = .Range(Format("J38","mm/dd/yyyy hh:mm"))
End With
With Sheets("Records").AutoFilter.Range
If crit5 = "" Then
If crit6 = "" Then
.AutoFilter Field:=16 'Both All - No filter
Else
.AutoFilter Field:=16, Criteria1:="<=" & crit6
End If
Else
If crit6 = "(All)" Then 'Filter on crit5 only
.AutoFilter Field:=16, Criteria1:=">=" & crit5
Else
.AutoFilter Field:=16, Criteria1:=">=" & crit5, Operator:=xlAnd,
Criteria2:="<=" & crit6
End If
End If
End With
Rgds