Setting Variable Range for use in filter

Clayton Michael

New Member
Dec 7, 2011
Hi All,
I'm very new to VBA and I'm trying to teach myself, but I've hit a wall.
Here's what I'm trying to do:

1. I'm trying to Copy a set of data from one workbook (working ok)
2. Paste that data to a different worksheet that already has other data starting with the first blank row (also working fine)
3. And finally I want the macro to filter what I just pasted for any rows where column K is equal to "0.00" then delete those rows (Problem)

My problem is with the following line:

Sheets("IMPORT DATA").Range("MyRange").AutoFilter Field:=9, Criteria1:="0.000"

I can't figure out how to name a variable range and get it to work with the AutoFilter.

If anyone can help with this or maybe suggest an easier way to do this I'd really appreciate it.

Here's the entire script I'm working with for reference:

Sub EasyInputData()
Dim MyRange As Range
' EasyInputData Macro

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("IMPORT DATA").Select
RowCount = Application.WorksheetFunction.CountA(Range("A:A"))
Range("A1").Offset(RowCount, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.AutoFilterMode = False
Range("A1").Offset(RowCount, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Set MyRange = Selection
Sheets("IMPORT DATA").Range("MyRange").AutoFilter Field:=9, Criteria1:="0.000"
Range("A1").Offset(RowCount + 1, 0).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

End Sub

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...