Setting Variable Range for use in filter

Clayton Michael

New Member
Joined
Dec 7, 2011
Messages
6
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("A126").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Watch MrExcel Video

Forum statistics

Threads
1,118,404
Messages
5,571,915
Members
412,426
Latest member
DeficientOptimism
Top