Thanks in advance.
Which of the following ways is the best coding convention and why?
Private Sub()
Sheets("DevData").Range("SourceDev").AdvancedFilte r Action:=xlFilterCopy,
CriteriaRange:=Sheets("FilterControl").Range("A28: Y29"), _
CopyToRange:=Sheets("Results").Range("A2:W2"), Unique:=False
end sub
Public Sub FilterDbase_Click()
Dim filtercontrolSheet As Worksheet
Dim resultsSheet As Worksheet
Dim devdataSheet As Worksheet
Set filtercontrolSheet = Sheets("FilterControl")
Set resultsSheet = Sheets("Results")
Set devdataSheet = Sheets("DevData")

devdataSheet.Range("SourceDev").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=filtercontrolSheet.Range("A28:Y29") , _
CopyToRange:=resultsSheet.Range("A2:W2"), Unique:=False
End Sub

Public Sub FilterDbase_Click()
Dim SourceRng
Dim CritRng
Dim CopyToRng
Set SourceRng = Sheets("DevData").Range("SourceDev")
Set CritRng = Sheets("FilterControl").Range("A28:Y29")
Set CopyToRng = Sheets("Results").Range("A2:W2")

SourceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, _
CopyToRange:=CopyToRng, Unique:=False
End Sub

I would prefer the last one but with the variables declared as Range coz

1. You are accessing the range object directly
2. Any changes to the code (sheetname or the range reference) at a later
stage would be easy to make ..Just one place to edit and that too at top of
the procedure..

Dim SourceRng As Range
Dim CritRng As Range
Dim CopyToRng As Range

--
Jacob


"RussellT" wrote:

Thanks in advance.
Which of the following ways is the best coding convention and why?
Private Sub()
Sheets("DevData").Range("SourceDev").AdvancedFilte r Action:=xlFilterCopy,
CriteriaRange:=Sheets("FilterControl").Range("A28: Y29"), _
CopyToRange:=Sheets("Results").Range("A2:W2"), Unique:=False
end sub
Public Sub FilterDbase_Click()
Dim filtercontrolSheet As Worksheet
Dim resultsSheet As Worksheet
Dim devdataSheet As Worksheet
Set filtercontrolSheet = Sheets("FilterControl")
Set resultsSheet = Sheets("Results")
Set devdataSheet = Sheets("DevData")

devdataSheet.Range("SourceDev").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=filtercontrolSheet.Range("A28:Y29") , _
CopyToRange:=resultsSheet.Range("A2:W2"), Unique:=False
End Sub

Public Sub FilterDbase_Click()
Dim SourceRng
Dim CritRng
Dim CopyToRng
Set SourceRng = Sheets("DevData").Range("SourceDev")
Set CritRng = Sheets("FilterControl").Range("A28:Y29")
Set CopyToRng = Sheets("Results").Range("A2:W2")

SourceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, _
CopyToRange:=CopyToRng, Unique:=False
End Sub

I agree - it makes the code much easier to understand, and so making changes
later become easier...not only for you, but for anyone maybe later in the sa

I'd have added a reference to the workbook too, but thats me being picky ;)

with ThisWorkbook
Set SourceRng = .Sheets("DevData").Range("SourceDev")
Set CritRng = .Sheets("FilterControl").Range("A28:Y29")
Set CopyToRng = .Sheets("Results").Range("A2:W2")
End With

"Jacob Skaria" wrote in message
...
I would prefer the last one but with the variables declared as Range coz

1. You are accessing the range object directly
2. Any changes to the code (sheetname or the range reference) at a later
stage would be easy to make ..Just one place to edit and that too at top
of
the procedure..

Dim SourceRng As Range
Dim CritRng As Range
Dim CopyToRng As Range

--
Jacob


"RussellT" wrote:

Thanks in advance.
Which of the following ways is the best coding convention and why?
Private Sub()
Sheets("DevData").Range("SourceDev").AdvancedFilte r Action:=xlFilterCopy,
CriteriaRange:=Sheets("FilterControl").Range("A28: Y29"), _
CopyToRange:=Sheets("Results").Range("A2:W2"), Unique:=False
end sub
Public Sub FilterDbase_Click()
Dim filtercontrolSheet As Worksheet
Dim resultsSheet As Worksheet
Dim devdataSheet As Worksheet
Set filtercontrolSheet = Sheets("FilterControl")
Set resultsSheet = Sheets("Results")
Set devdataSheet = Sheets("DevData")

devdataSheet.Range("SourceDev").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=filtercontrolSheet.Range("A28:Y29") , _
CopyToRange:=resultsSheet.Range("A2:W2"), Unique:=False
End Sub

Public Sub FilterDbase_Click()
Dim SourceRng
Dim CritRng
Dim CopyToRng
Set SourceRng = Sheets("DevData").Range("SourceDev")
Set CritRng = Sheets("FilterControl").Range("A28:Y29")
Set CopyToRng = Sheets("Results").Range("A2:W2")

SourceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng,
_
CopyToRange:=CopyToRng, Unique:=False
End Sub

Thanks for the input. I understand the making changes to the code in the
futre would be easier using option #3, but do are any of the formats more
readily identified by vba? thanks again.

"Jacob Skaria" wrote:

I would prefer the last one but with the variables declared as Range coz

1. You are accessing the range object directly
2. Any changes to the code (sheetname or the range reference) at a later
stage would be easy to make ..Just one place to edit and that too at top of
the procedure..

Dim SourceRng As Range
Dim CritRng As Range
Dim CopyToRng As Range

--
Jacob


"RussellT" wrote:

Thanks in advance.
Which of the following ways is the best coding convention and why?
Private Sub()
Sheets("DevData").Range("SourceDev").AdvancedFilte r Action:=xlFilterCopy,
CriteriaRange:=Sheets("FilterControl").Range("A28: Y29"), _
CopyToRange:=Sheets("Results").Range("A2:W2"), Unique:=False
end sub
Public Sub FilterDbase_Click()
Dim filtercontrolSheet As Worksheet
Dim resultsSheet As Worksheet
Dim devdataSheet As Worksheet
Set filtercontrolSheet = Sheets("FilterControl")
Set resultsSheet = Sheets("Results")
Set devdataSheet = Sheets("DevData")

devdataSheet.Range("SourceDev").AdvancedFilter Action:=xlFilterCopy,
CriteriaRange:=filtercontrolSheet.Range("A28:Y29") , _
CopyToRange:=resultsSheet.Range("A2:W2"), Unique:=False
End Sub

Public Sub FilterDbase_Click()
Dim SourceRng
Dim CritRng
Dim CopyToRng
Set SourceRng = Sheets("DevData").Range("SourceDev")
Set CritRng = Sheets("FilterControl").Range("A28:Y29")
Set CopyToRng = Sheets("Results").Range("A2:W2")

SourceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, _
CopyToRange:=CopyToRng, Unique:=False
End Sub