Thanks in advance.
Which of the following ways is the best coding convention and why?
Private Sub()
Sheets("DevData").Range("SourceDev").AdvancedFilter 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").AdvancedFilter 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" <(E-Mail Removed)> wrote in message
news:C21C8863-8DE9-4450-BE95-(E-Mail Removed)...
> 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").AdvancedFilter 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").AdvancedFilter 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
> >