• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to use custom filter Less than option thru macro

A

Akash kothari

Member
Hello Friend,

1) I have issue, I receive a filtered Excel (.xls) file named as "commentary report" where i again do a custom file while enter a number in this message box.

Dim cutoff As Integer

cutoff = Application.InputBox("Please Enter the reporting BD", _
Title:="Please Enter the reporting BD", Default:="Please Enter the reporting BD")

If cutoff = 0 Then Exit Sub


Let say i have to filter for 8 in column E using less than custom filter option it should filter for all the value less than 8. then i want to copy only the visible cells from these column A,B,C,D, F, L, R and T and paste on new workbook. " the value 8 can be repetative and not neccessarily in the numeric order.


2) Have done till below


Dim cutoff1 As Integer

(but when it comes to)

Selection.AutoFilter field:=5, Criteria1:="<" & CStr(cutoff1), Operator:=xlAnd


blank cell are getting selected and not the visible cells

where i am worng ???




what should i change to the Highlighted part "date" into in this formula if i want to use numbers like 1,2,11,12 till 25 this number will put by me manually daily into the above Msg box (mentioned under point 1)


(ActiveSheet.Range("$A$1:$BL$164").AutoFilter Field:=5, criteria1:="<" & date, Operator:=xlAnd)

please view the attached file

Thank you
akash
 

Attachments

  • Template.xlsm
    19.4 KB · Views: 8
Hi Akash ,

I am not clear on your requirement ; the following set of statements :

Code: 
Dim cutoff1 As Integer
 
Selection.AutoFilter field:=5, Criteria1:="<" & CStr(cutoff1), Operator:=xlAnd
works perfectly well.

You have not mentioned whether the blank cells are part of the data range or outside of the data range , since your statement uses Selection ; if the selected range has blank cells outside of the data range , they will very much be part of the filtered range , since the Autofilter part extends only as far as the table of data.

In case you wish to add a second criterion , you can change your statement to :
Code: 
Selection.AutoFilter field:=5, Criteria1:="<" & [O1], Operator:= _
                                                                xlAnd, Criteria2:=">=" & CStr(cutoff2)

In case you wish to modify the criterion value often , it is better to use a cell to enter this value , and use the cell address in your VBA code e.g.

Selection.AutoFilter field:=5, Criteria1:="<" & [O1], Operator:=xlAnd

will use the value entered in cell address O1.

Narayan
 
Hi Narayan,

Thank for your response, i am not really good in Macro just a new beginner, but what ever i have done as you can see in attached template.
below are point what i am trying to do

1) In the below Macro i have created a MSG box which ask to open Excel "commentary file".
2) and then In the "Commentary file" under E column number will be there like 1 to 25 , so daily i have filter this file using "Custom filter less than option" and select only the visible cells and paste in the new workbook. (note number under E column can be repetitive and not necessarily in the numeric order)

Sub Master_list_template()

Dim sfile(1 To 2) As Variant, workbookname(1 To 4) As String, sheetname(1 To 4) As String

workbookname(1) = ActiveWorkbook.Name

sfile(1) = Application.GetOpenFilename(fileFilter:="Excel Files (*.xlsx), *.xlsx", _
Title:="Select Commentary Daily Update File", MultiSelect:=False)
If sfile(1) = 0 Then
MsgBox "Select Commentary Daily Update File"
sfile(1) = Application.GetOpenFilename(fileFilter:="Excel Files (*.xlsx), *.xlsx", _
Title:="Select Commentary Daily Update File", MultiSelect:=False)
If sfile(1) = 0 Then Exit Sub
End If
Workbooks.Open Filename:=sfile(1)
workbookname(2) = ActiveWorkbook.Name

3) Below msg box i have created to enter the number.

Dim cutoff As Integer
cutoff = Application.InputBox("Please Enter the reporting BD", _
Title:="Please Enter the reporting BD", Default:="Please Enter the reporting BD")
If cutoff = 0 Then Exit Sub

4) below i have created below so that excel filter column E for all the value "Less than". Let say i have enter 5.
it should select all the number less than from column E, but when excel reads the below line my data just goes off and excel copy pasting the blank cell in new work book.

Dim cutoff1 As Integer
Selection.AutoFilter field:=5, Criteria1:="<" & CStr(cutoff1), Operator:=xlAnd
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy

i feel there is something wrong in defining the 2nd critiria (cutoff1) as a number, do i need to tell the cutoff1 is number ??? if yes how ??
see if you can help me
Thank you
Akash kothari
 
Hi Akash ,

The file you have uploaded contains only the macro ; I have already mentioned in my earlier post that the Autofilter statement works perfectly fine ; if you can upload any one of the data files , then testing will be easier and probably more thorough.

Can you upload any one of the Commentary Daily Update Files ?

Narayan
 
attached is the dummy file of what i receive daily. file already have filtered applied on it. and i guess thats the problem and there will be hidden rows in file too.
 

Attachments

  • Copy of commentary.xlsx
    9.6 KB · Views: 9
what i feel is do i define cutoff1 as a number before putting it under criteria ??


Selection.AutoFilter field:=5, Criteria1:="<" & CStr(cutoff1), Operator:=xlAnd
 
Hi Akash ,

Thanks for uploading your file ; let me go through it and get back to you.

You have declared cutoff1 as Integer ; in the absence of an assignment , its value will be 0.

Narayan
 
Hi Narayan,
You are almost there, just little thing is that its copying the hidden rows as well if you see in image 1 there is a hidden "row 5" . in image 2 we can see that there are only 4 rows that are there for less than 6. In image 3 when i enter "Less than criteria as 6" . Image 4 its copying the hidden row as well total 5 rows are there.
Any suggestion why it is so.

View attachment 464 View attachment 465 image 3.jpg image 4.jpg

Thank you
Akash
 
Hi Narayan,

Yes I agree with you that there are 5 rows with reporting BD less than 6. but if you see in the image1 that 5th row is hidden, and i only want to copy visible rows data.

Untitled1.jpg Untitled2.jpg
 
Hey i think i fixed it

Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.UsedRange.AutoFilter field:=5, Criteria1:="<" & cutoff, Operator:=xlAnd
ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Thank for your help
Akash
 
Hi Akash ,

I don't understand what you are doing ; I suggest that any coding should not mix up things ; if you are filtering data using criteria , then where is the question of rows being hidden ? The result of the filter will decide which rows are visible and which are not ; there should not be 5 rows which meet the given criterion , and only 4 rows which are visible. In such a case , add more critieria , if necessary to the other columns , so that one Autofilter command will generate the required output.

If you are hiding rows , then use criteria to hide those rows which you want hidden , so that one process decides the outcome.

Narayan
 
You must log in or register to reply here.
Back
Top