Excel vba is changing the Range I set-CodePudding

I was trying to set the following Range:

Range("F3:F102,J3:J102,N3:N102,R3:R102,V3:V102,Z3:Z102,AD3:AD102,AH3:AH102,AL3:AL102,AP3:AP102,AT3:AT102,AX3:AX102,BB3:BB102,BF3:BF102,BJ3:BJ102,BN3:BN102,BR3:BR102,BV3:BV102,BZ3:BZ102,CD3:CD102")

I tried to build it iteratively using a For Loop and Application.Union, instead of typing the Range manually because I have to build many ranges like that one and it would take me forever to type them all, so I used the following code:

Sub Build_Range()

    Dim FirstParamCol, ParamCells                             As Range
    Dim i                                                     As Integer

    
    Application.ScreenUpdating = False
    Application.EnableEvents = False 'avoid infinite loop if any target cell is changed

    
    Set ParamCells = Range("F3:F102") 'There 20 different "Parameter" columns, the first one being Column F (column 6)
    
    'Note: After column F (6), the other 19 columns are 4 columns apart each (j/10, N/14, ...)
    'so I looped for i = 1 to 19 and used the formula column = 6 (column F)   i * 4:
    
    For i = 1 To 19 'There are other 19 "Parameter" columns
        Set ParamCells = Application.union(ParamCells, Range(ActiveSheet.Cells(3, 6   4 * i), ActiveSheet.Cells(102, 6   4 * i)))
    Next i
    MsgBox ParamCells.Address 'TODO: For Debugging only
    Range("B103").Value = ParamCells.Address
    
Exitsub:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  
End Sub

After running the above code, I'm getting the following range, which is missing the last column ("CD3:CD102"):

Range("F3:F102,J3:J102,N3:N102,R3:R102,V3:V102,Z3:Z102,AD3:AD102,AH3:AH102,AL3:AL102,AP3:AP102,AT3:AT102,AX3:AX102,BB3:BB102,BF3:BF102,BJ3:BJ102,BN3:BN102,BR3:BR102,BV3:BV102,BZ3:BZ102")

I tried increasing the last i value in the For Loop, but I keep getting the same range as above.

I even tried the following code to Set the range manually and still, I'm getting the same range as above, again missing the last column:

Sub Build_Range_2()

    Dim ParamCells                             As Range

    
    Set ParamCells = Range("F3:F102,J3:J102,N3:N102,R3:R102,V3:V102,Z3:Z102,AD3:AD102,AH3:AH102,AL3:AL102,AP3:AP102,AT3:AT102,AX3:AX102,BB3:BB102,BF3:BF102,BJ3:BJ102,BN3:BN102,BR3:BR102,BV3:BV102,BZ3:BZ102,CD3:CD102")
    MsgBox ParamCells.Address
    Range("B103").Value = ParamCells.Address
  
End Sub

After I set the Rage manually and still excel/vba changes the range, I just got frustrated. I searched to see if it was some kind of limit in the maximum size of a Range or something similar but I couldn't find anything.

CodePudding user response：

First the good information is that your code building that range works properly!

Insert a ParamCells.Select and check what it selects. Even "CD3:CD102" does not show up in the address ParamCells.Address it is part of the range ParamCells.

The issue is that .Address is limited to 255 characters.

You can easily check that if you compare

Debug.Print ParamCells.Address
Debug.Print ParamCells.Address(False, False)

$F$3:$F$102,$J$3:$J$102,$N$3:$N$102,$R$3:$R$102,$V$3:$V$102,$Z$3:$Z$102,$AD$3:$AD$102,$AH$3:$AH$102,$AL$3:$AL$102,$AP$3:$AP$102,$AT$3:$AT$102,$AX$3:$AX$102,$BB$3:$BB$102,$BF$3:$BF$102,$BJ$3:$BJ$102,$BN$3:$BN$102,$BR$3:$BR$102,$BV$3:$BV$102,$BZ$3:$BZ$102
F3:F102,J3:J102,N3:N102,R3:R102,V3:V102,Z3:Z102,AD3:AD102,AH3:AH102,AL3:AL102,AP3:AP102,AT3:AT102,AX3:AX102,BB3:BB102,BF3:BF102,BJ3:BJ102,BN3:BN102,BR3:BR102,BV3:BV102,BZ3:BZ102,CD3:CD102

The first has 253 characters and adding CD3:CD102 to the string would exceed 255, therefore it is truncated. The second has no absolut address and therefore it is shorter so in the end you can see …,BZ3:BZ102,CD3:CD102 that CD3:CD102 is actually in the range.

So any time you work with ParamCells it works with the full range no matter what ParamCells.Address is truncated to.

So the question is do you need to show the address? Then you would need some workaround (depending on what your actual goal is). Or was it only to debug your code then use ParamCells.Select to check it.

If you want to output the actual address of ParamCells vor verification, you can do that by looping through the ParamCells.Areas.

Dim AddrOfParamCells As String
Dim Area As Range
For Each Area In ParamCells.Areas
    AddrOfParamCells = IIf(AddrOfParamCells <> vbNullString, AddrOfParamCells & ",", vbNullString) & Area.Address
Next Area

Debug.Print AddrOfParamCells

Note that this address can only be used for verifying but you can not use it to build a range like Set TestRange = Range(AddrOfParamCells) because it exceeds 255 charaters.

CodePudding user response：

The .Address is limited to 255 characters... But you can handle using a String.

    Sub Build_Range()

    Dim FirstParamCol   As Range
    Dim ParamCells      As Range
    Dim i               As Integer
    Dim StrRange        As String 'String to store the address of ParamCells

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'Is not a good practice to hard code... but it is your code, and
    'You will debug this... in the future!
    Set ParamCells = Range("F3:F102")
    StrRange = ParamCells.Address(1, 1, xlA1, 0, 0)
    'To find some help in .Address:
    'https://docs.microsoft.com/en-us/office/vba/api/excel.range.address
    
    For i = 1 To 19
        'Here you store the address.
        'Again, don't like to hard code... but the & "," & in this point it is necessary
        StrRange = StrRange & "," & Range(ActiveSheet.Cells(3, 6   4 * i), ActiveSheet.Cells(102, 6   4 * i)).Address(1, 1, xlA1, 0, 0)
        'I Keep youy var, if you want to do something with the range.
        'You can not print the full address in the immediate window, but
        'You can still use the full range.
        Set ParamCells = Application.Union(ParamCells, Range(ActiveSheet.Cells(3, 6   4 * i), ActiveSheet.Cells(102, 6   4 * i)))
    Next i
    
    'Just to check if is working.
    'ParamCells.Select
    
    MsgBox ParamCells.Address 'TODO: For Debugging only
    'No use this
    'Range("B103").Value = ParamCells.Address
    'Use this:
    Range("B103").Value = ParamCells.Address
    
Exitsub:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
  
End Sub