Avoiding repeating code in VBA

[berian]

Hi all,

I'm currently trying to get my head around avoiding copying and pasting the same code, but looking into functions and sub routines, as got me a bit baffled.

Would anyone be able to help show me how I could avoid repeating myself in the example code below. Three different ranges that get duplicates highlighted.

Sub HightlightDuplicates()
    Dim Rng As Range
    Dim Unq As UniqueValues

    Set Rng = MasterStock.Range("A3:A50000")
    Set Unq = Rng.FormatConditions.AddUniqueValues
    Unq.DupeUnique = xlDuplicate
    Unq.Interior.Color = 13551615
    Unq.Font.Color = RGB(156, 0, 6)
       
    Set Rng = MasterStock.Range("F3:F50000")
    Set Unq = Rng.FormatConditions.AddUniqueValues
    Unq.DupeUnique = xlDuplicate
    Unq.Interior.Color = 13551615
    Unq.Font.Color = RGB(156, 0, 6)
       
    Set Rng = MasterStock.Range("AD3:AD50000")
    Set Unq = Rng.FormatConditions.AddUniqueValues
    Unq.DupeUnique = xlDuplicate
    Unq.Interior.Color = 13551615
    Unq.Font.Color = RGB(156, 0, 6)

End Sub

Much Appreciated,
Berian

 

[kevin9999]

Hi Berian,

Try changing this:

Set Rng = MasterStock.Range("A3:A50000")

To this

Set Rng = MasterStock.Range("A3:A50000,F3:F50000,AD3:AD50000")

and use your formatting block just once.

 

[cmowla]

Even though I liked this response a lot:

        Set Rng = MasterStock.Range("A3:A50000,F3:F50000,AD3:AD50000")

Note that each of those are called "areas" of the range. (There are 3 areas in the above range.) There is a limit to the number of areas you can have in a range before Excel throws an error. (It's 51 in 365, I believe, but I believe it was 28 in 2016.)

So, on the other hand, using a loop (which allows ANY number of broken up ranges) is safest. And this is a very condensed way to go about it.

Sub test()

Dim Rng As Range
Dim Unq As UniqueValues

Dim arrayString() As String, i As Integer
arrayString = Split("A,F,AD", ",") 'Put as many column letters here in a comma list as you need.

For i = 0 To UBound(arrayString)
    Set Rng = MasterStock.Range(arrayString(i) & "3" & ":" & arrayString(i) & "50000")
    Set Unq = Rng.FormatConditions.AddUniqueValues
    Unq.DupeUnique = xlDuplicate
    Unq.Interior.Color = 13551615
    Unq.Font.Color = RGB(156, 0, 6)
Next i

End Sub

 

[RoryA]

There is a limit to the number of areas you can have in a range before Excel throws an error. (It's 51 in 365, I believe, but I believe it was 28 in 2016.)

That's not actually true. There is, however, a limit of 255 characters for the address string that you can pass as the Range argument.

 

[cmowla]

That's not actually true. There is, however, a limit of 255 characters for the address string that you can pass as the Range argument.

Oh, okay. So if done progamatically with Union(), there is no limit to the number of areas? But in this thread, the proposed solution was writing: Range("A3:A50000,F3:F50000,AD3:AD50000"). Wouldn't that count as being a limit in this sense? Each of those areas are 9 characters. Including the commas, it's roughly just 28 total allowed, right?

 

[RoryA]

In this particular instance, if they were all that length, then yes, but since that is not what you said (and it wouldn't differ between versions), I felt it important to make the correction.