Eliminate redundancy in Excel VBA Code

The code below obviously works, but I'm wondering if there is perhaps a more efficient way that it can be written?  Thanks!
Public Sub ApplyFillFormat()
Dim rngAutoFill1, rngAutoFill2, rngAutoFormat As Range
Set rngAutoFill1 = Range("C14:H14")
Set rngAutoFill2 = Range("K14:S14")
Set rngAutoFormat = Range("I14")

Range(rngAutoFill1, rngAutoFill1.End(xlDown)).FillDown
Range(rngAutoFill2, rngAutoFill2.End(xlDown)).FillDown

rngAutoFormat.Copy
Range(rngAutoFormat, rngAutoFormat.End(xlDown)).PasteSpecial (xlPasteFormats)

End Sub

Open in new window

KP_SoCalAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sir plusConnect With a Mentor Sales ManagementCommented:
Or even better

Public Sub ApplyFillFormat()
Dim rngAutoFill As Variant
Dim r As Variant
Dim rngAutoFormat As Range
 
Let rngAutoFill = Array("C14:H14", "K14:S14")
Set rngAutoFormat = Range("I14")
    
For Each r In rngAutoFill
    Range(Range(r), Range(r).End(xlDown)).FillDown
Next r

rngAutoFormat.Copy
Range(rngAutoFormat, rngAutoFormat.End(xlDown)).PasteSpecial (xlPasteFormats)
 
End Sub

Open in new window

0
 
Patrick MatthewsConnect With a Mentor Commented:
KP_SoCal,

If you a large number of ranges to apply that to, then you might want to try looping through an array, but as it is your code is fine, with the exception of:

Dim rngAutoFill1, rngAutoFill2, rngAutoFormat As Range


When you do that, rngAutoFill1 and rngAutoFill2 are actually being declared as Variant, and not as Range.  Instead, use:

Dim rngAutoFill1 As Range, rngAutoFill2 As Range, rngAutoFormat As Range

Patrick
0
 
KP_SoCalAuthor Commented:
Patrick, thanks for the declaration tip!  Would you be able to give me an example of how an array would look if applied to my code?  Just curious in case I ever need it in the future.  Thanks so much! =)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
sir plusSales ManagementCommented:
Hi
Your current code is fine for 2 areas abut for 10 it will get messy
to loop etc you need to use variant

I think this is what you are after

PS Best practice is to define 1 per line for readability, not sure I agree but thats what I do.


Public Sub ApplyFillFormat()
Dim rngAutoFill As Variant
Dim r As Variant
Dim rngAutoFormat As Range

Let rngAutoFill = Array("C14:H14", "K14:S14")
Set rngAutoFormat = Range("I14")
 

For r = LBound(rngAutoFill) To UBound(rngAutoFill)
    Range(Range(rngAutoFill(r)), Range(rngAutoFill(r)).End(xlDown)).FillDown
Next r
 
rngAutoFormat.Copy
Range(rngAutoFormat, rngAutoFormat.End(xlDown)).PasteSpecial (xlPasteFormats)

End Sub

Open in new window

0
 
KP_SoCalAuthor Commented:
Thanks guys for the feedback on this!  I'm going to split the points up.
0
 
sir plusSales ManagementCommented:
Thaks for the recognition
0
All Courses

From novice to tech pro — start learning today.