[RESOLVED] Excel2007 -Issues with Large Select Case Row height & 200 NonContiguous Ranges

[SQLADOman]

Good day to all,

I have shown below an abbreviated version of a very long routine that I am having some issue with it running fine for the 1st use, and very slow if run a 2nd time. -- If running it once, it processes everything in about 2 or 3 seconds which is great, however if I try to run it a second time it bogs down and can take up to one or two minutes. If I close the file and reopen it, it's always the same: fast the 1st run and extremely slow the 2nd.

Application.ScreenUpdating = False

 With ActiveSheet 
 
    .EnableCalculation = False
    
    .Range("e108").NumberFormat = "General"
    .Range("e108").Value = "=$i$4"
    .Range("f215").NumberFormat = "General"
    .Range("f215").Value = "=$i$4"
    .Range("f224").NumberFormat = "General"
    .Range("f224").Value = "=$i$4"
     '.......................
    'continues with about 100 more like above 
    '.......................   
  
    .Range("a223").NumberFormat = "General"
    .Range("a223").FormulaR1C1 = "=CONCATENATE(""To: "",R[-212]C[2])"
    .Range("a231").NumberFormat = "General"
    .Range("a231").FormulaR1C1 = "=CONCATENATE(""To: "",R[-220]C[2])"
    '.......................
    'continues with a couple dozen more like above (Non-Contiguous ranges)
    '.......................
 End With    
    
Dim x As Long
For x = 0 To 260
   
Select Case Range("A1").Offset(x, 0).Row
Case 1
    Range("A1").Offset(x, 0).RowHeight = 45.75
Case 2
    Range("A1").Offset(x, 0).RowHeight = 96
Case 3
    Range("A1").Offset(x, 0).RowHeight = 19.5
Case 4
    Range("A1").Offset(x, 0).RowHeight = 21
Case 5
    Range("A1").Offset(x, 0).RowHeight = 18.75
Case 6
    Range("A1").Offset(x, 0).RowHeight = 23.25
'.......................
'continues to case 259
'.......................
    
End Select
Next

Application.ScreenUpdating = True
  
With ActiveSheet
      .EnableCalculation = True
End With 

MsgBox ("Finished Reseting Formulas, and Row Heights")

For Non-Contiguous ranges can I use something like this? (I think I have the syntax wrong)

    With
        Union(Range("e108,f215,f242,B43,C43,D43,B44,C44"))
        .NumberFormat = "General"
        .Value = "=$i$4"
    End With

And I would like some suggestions on another method that would run more efficiently than my very long Select Case statement.

Thanks in advance