How can I efficiently add a variable value to a combined range in VBA?

Adding a value to a combined range in VBA

I am trying to add a variable value to a range, which consist of multiple ranges. Using "for each cell" is super slow and takes forever. I managed to use this code successfully when the range is not consisting of multiple ranges, now, using a combination of ranges, the macro finishes without errors - but looking at the cells I am trying to change, there is a #VALUE error.

Sub rangecheck()

Dim x As Long
Dim number_of_tenants As Long
Dim range_names() As String
Dim ranges() As Range
Dim dynamic_range As Name

number_of_tenants = 5
ERV_change = 0.1

ReDim range_names(1 To number_of_tenants)
ReDim ranges(1 To number_of_tenants)

    For x = 1 To number_of_tenants
    range_names(x) = "range" & x
    Set ranges(x) = Range("F" & 21 + 172 * (x - 1) & ":AJ" & 21 + 172 * (x - 1))
    Set dynamic_range = ThisWorkbook.Names.Add(Name:=range_names(x), RefersTo:=ranges(x))
    Next x

Set combined_range = Range("range1")
    For x = 2 To number_of_tenants
        Set current_range = Range("range" & x)
        Set combined_range = Union(combined_range, current_range)
    Next x
    
combined_range.Value = Evaluate(combined_range.Address & "+" & CStr(ERV_change))

End Sub

Thanks for any help.

As @TimWilliams mentioned the following is pretty fast: On my machine it took 2.94 seconds for 5000 tenants, whereas your code took 109.52 seconds. Furthermore, it took 9.09 seconds when the naming line is uncommented.

Sub rangecheck()
  Dim start As Double: start = Timer
  
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  
  Dim x As Long
  Dim number_of_tenants As Long:  number_of_tenants = 5
  Dim ERV_change As Double:       ERV_change = 0.1
  Dim row_offset As Long:         row_offset = 21
  Dim sht As Worksheet:           Set sht = ThisWorkbook.Sheets("enterSheetNameHere")
  
  For x = 1 To number_of_tenants
    With sht.Range("F" & row_offset & ":AJ" & row_offset)
      .Value = Evaluate("=" & sht.Name & "!" & .Address & "+" & ERV_change)
      '* If you have to name the ranges then uncomment the following line
      '.Name = "range" & x
    End With
    row_offset = row_offset + 172
  Next x
  
  Application.ScreenUpdating = True
  Application.EnableEvents = True

  MsgBox "Time taken: " & Format(Timer - start, "0.0000")
End Sub

I propose the following corrections to your code:

Option Explicit

Public Sub rangecheck()
    Dim ERV_change As Double: ERV_change = 0.1
    Dim number_of_tenants As Long: number_of_tenants = 5
    Dim range_names() As String: ReDim range_names(1 To number_of_tenants)
    Dim ranges() As Range: ReDim ranges(1 To number_of_tenants)
    
    Dim x As Long: For x = 1 To number_of_tenants
        range_names(x) = "range" & x
        Set ranges(x) = Range("F" & 21 + 172 * (x - 1) & ":AJ" & 21 + 172 * (x - 1))
        Dim dynamic_range As Name: Set dynamic_range = ThisWorkbook.Names.Add(Name:=range_names(x), RefersTo:=ranges(x))
    Next x
    
    Dim combined_range As Range: Set combined_range = Range("range1")
    For x = 2 To number_of_tenants
        Dim current_range As Range: Set current_range = Range(range_names(x))
        Set combined_range = Union(combined_range, current_range)
    Next x
    
    Dim a As Long: For a = 1 To combined_range.Areas.Count
        Dim temp() As Variant: temp = combined_range.Areas(a).Value
        Dim r As Long: For r = LBound(temp, 1) To UBound(temp, 1)
            Dim c As Long: For c = LBound(temp, 2) To UBound(temp, 2)
                temp(r, c) = Val(temp(r, c)) + ERV_change
            Next c
        Next r
        combined_range.Areas(a).Value = temp
    Next a
End Sub

Note that the Range object has an Areas property, which consists of multiple Range objects, if the Range in question was created with the Union function as in the case of combined_range. The Value of each of these is a two dimensional array, which can be retrieved and overwritten at once.

If you try to simply read or write combined_range.Value that is the same as reading or writing combined_range.Areas(1).Value.

So you have to iterate over this Area property. However, inside each of these, all cells will be written with one call between VBA and Excel, therefore this will produce the best available performance in this situation, better than the "for each cell" solution you understandably want to avoid.