Hi David,

Try:

Dim rng1 As Range, rng2 As Range

With Sheets("source data")
Set rng1 = .Range(.Range("A1"), _
.Cells(Rows.Count, "A").End(xlUp))
End With

With Sheets("criteria")
Set rng2 = Range(.Range("A1"), _
.Cells(1, Columns.Count).End(xlToLeft))
End With


---
Regards,
Norman



"david shapiro" wrote in message
...
Hi,

The first two lines of this code are currently hardcoded for a dataset
which has the range exactly of A1:I2754 and a criterial dataset which
has a range of A1:G5.

I`ve seen in another message about set rng =
activesheet.range("A1").currentregion.
But am new to VB and not sure how to do this.
How could the following code be adjusted so that it automatically takes
the full range of the datasets which contains data (no matter the size),
and the same for the criteria dataset?

David Shapiro

Sheets("source data").Select
Range("A1:I2754").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Sheets("criteria").Range("A1:G5"), Unique:=False

Sheets.add
Sheets("source data").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!