CODEWITHSUNDEEP
vbaexcel
Ryszard Jędraszyk
Ryszard Jędraszyk

Reputation: 2412

VBA - the same formatting for multiple dynamic ranges

I want to format data in 2 columns in the same pattern. Each data column has its length based on upper boundary of result array. I initially formatted them both separately and it was working as intended, but I want to keep the code as lean as possible.

I tried the code below, but it created a range from 1st range to the 2nd instead of matching a sum of these ranges: 

With statsWS
    With Range(.Range("b2:c" & UBound(vGoals) + 1), _
               .Range("e2:f" & UBound(vAssists) + 1))
        With .Borders
            .LineStyle = xlContinuous
            .Color = rgbGrey
        End With
    End With
End With

Upvotes: 0

Views: 89

Answers (3)

user6432984
user6432984

Reputation:

You could use Chris Neilsen's suggestion:

With statsWS
    With Union(.Range("B2:C" & UBound(vGoals) + 1), .Range("E2:F" & UBound(vAssists) + 1))
        With .Borders
            .LineStyle = xlContinuous
            .Color = rgbGrey
        End With
    End With
End With

But if you want to keep your code lean then you could pass the ranges to another Subroutine to handle the formatting. Separating the business logic from the display:

Usage:

ApplyBorders .Range("B2:C" & UBound(vGoals) + 1), .Range("E2:F" & Bound(vAssists) + 1)

Code:

Sub ApplyBorders(ParamArray Ranges())
    Dim x As Long
    Dim r As Range
    Set r = Ranges(0)

    For x = 1 To UBound(Ranges())
        Set r = Union(r, Ranges(x))
    Next

    With r.Borders
        .LineStyle = xlContinuous
        .Color = rgbGrey
    End With
End Sub

Note: Because ApplyStandardBorders uses a ParamArray you can pass anywhere from 0 to 60 parameters to it (Only 29 in Excel 2003).

Upvotes: 1

user3598756
user3598756

Reputation: 29421

you can also use the Range("Address1,Address2") method to get the union of different ranges

With statsWS
    With .Range(.Range("b2:c" & UBound(vGoals) + 1).Address & "," & .Range("e2:f" & UBound(vAssists) + 1).Address).Borders
        .LineStyle = xlContinuous
        .Color = rgbGrey
    End With
End With

Upvotes: 1

Slai
Slai

Reputation: 22886

something like this:

With statsWS.Range("b2:c" & (UBound(vGoals) + 1) & ",e2:f" & (UBound(vAssists) + 1)).Borders
    .LineStyle = xlContinuous
    .Color = rgbGrey
End With

Upvotes: 2

Related Questions