.net Excel Range Address limitation

Question

I want to define multiple areas in one single Excel.Range object. The purpose is to colorize multiple different areas by setting one single Range. This should save time using the Excel interop, which is very slow in such operations. The problem is, that I get an error (HRESULT: 0x800A03EC) when I try to put a "big" address line into the Range. Could somebody tell me if there is a limitation using Excel interop and does anybody have a solution for colorizing lots of areas at once / in a fast manner?

The "big" address line in the example is just to show you where the problem is. I know it does not make a lot of sense to put A1:A2 multiple times into the address.

Dim objExcelApp As New Excel.Application

objExcelApp.Visible = True

Dim objExcelWorkbooks As Excel.Workbooks = objExcelApp.Workbooks

Dim objExcelWB As Excel.Workbook = objExcelWorkbooks.Add

Dim objExcelWS As Excel.Worksheet = objExcelWB.Worksheets(1)

Dim rng As Excel.Range
rng = objExcelWS.Range("A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2;A1:A2")

Answer 1

I found a really fast possibilty in filling a multi-area range with more content in using the Union function in combination with the approach of splitting the address into chunks of 255 char strings. This function does the job very well. This code is with semicolon, because its a country specific seperator it seems (comma is not working in my case, maybe you can modify it). Thanks @K.Dᴀᴠɪs for the hint:

Private Function CombineAddressToRange(ByVal Address As String, ByVal objExcelWorksheet As Excel.Worksheet, ByVal objExcelApp As Excel.Application) As Excel.Range

        Dim SplitAddress As String()
        Dim TempAddress As String = ""
        Dim FinalRange As Excel.Range

        SplitAddress = Address.Split(";")

        'Initialize Range
        FinalRange = objExcelWorksheet.Range(SplitAddress(0))

        If UBound(SplitAddress) >= 1 Then
            For i = 1 To UBound(SplitAddress)

                If Len(TempAddress) + 1 + Len(SplitAddress(i)) > 255 Then
                    FinalRange = objExcelApp.Union(FinalRange, objExcelWorksheet.Range(TempAddress))
                    TempAddress = SplitAddress(i)
                Else
                    If TempAddress = "" Then
                        TempAddress = SplitAddress(i)
                    Else
                        TempAddress = TempAddress & ";" & SplitAddress(i)
                    End If

                End If

            Next

            If TempAddress <> "" Then
                FinalRange = objExcelApp.Union(FinalRange, objExcelWorksheet.Range(TempAddress))
            End If

        End If

        Return FinalRange

    End Function