how to change the range

[david shapiro]

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

 

[Norman Jones]

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

 

[david shapiro]

Norman,

Thanks for the suggestion. How would it work though with an advanced
filter command such as:

Sheets("source data").Select

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

How do I incorporate the range codes you mentioned in that?

Dave

 

[Norman Jones]

Hi David,

Something like this may work for you:

Sub Tester01()
Dim rngData As Range, RngCrit As Range
Dim rngOutput As Range

With ActiveWorkbook
With .Sheets("Source Data")
Set rngData = .Range("A1").CurrentRegion '<<CHANGE
End With

With .Sheets("Output")
Set RngCrit = .Range("H1").CurrentRegion '<<CHANGE
Set rngOutput = .Range("A1") '<<CHANGE
End With

.Sheets("Output").Activate
rngData.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=RngCrit, _
CopyToRange:=rngOutput, _
Unique:=True
End With
End Sub

Amend the sheet names and ranges to suit your needs.