Re: Using VBA to filter where criteria is variable
Hello again,
I am attempting to filter a file using VBA and the criteria will vary each time the macro is run. Also once filtered I will need to select the filtered cells and delete the rows, of course the range will vary depending on the filter criteria.
Is there a way to write code for this?
Thank you for your assistance.
I am working with a spreadsheet to produce a report. The data will vary depending on the department for which I am reporting. I need to select the records in column A that are the result of a filter and paste them in column B, once pasted I need to clear the cells in column A then remove the filter. I have been able to accommodate the variable criteria for the filter; however, my problem is with selecting the variable range. The data to be selected will never start in cell A2, any cell after that is fair game. How do I select a range that varies? How do I have the User interact with the macro to select the cells?</SPAN>
This works for me to filter by all my variables.</SPAN>
Cells.Select</SPAN>
Selection.AutoFilter</SPAN>
ActiveSheet.Range("$A$1:$F$65000").AutoFilter Field:=1, Criteria1:=Array( _</SPAN>
"1000", "1001", "1005", "ZBIL", "1002", "1003", "1004", "1006", "1007", "1008", "1009", "AOMS", "ASPS", "NATL", "ZCON", "ZREP"), Operator:=xlFilterValues</SPAN>
On Error Resume Next
I am using the following to select the range, the first line works to select the data to be copied (although it still selects the cell A1 and I would prefer not to have that cell selected). The second line returns an error "Object doesn't support this property or method. I have tried range select ToRight however I end up with the entire sheet selected.
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection(Selection, Selection.xlToRight).Select
</SPAN> Selection.FillRight
I appreciate any assistance that is available.
