[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

[SQLADOman]

I did some goggling and saw some code with the declaration's different.
Like this:

Dim myRows As Double
myRows = .Cells(Row, 1).Row
Select Case myRows

So I Changed that.

Also the other code I saw used .Cells(Row, 1) instead of Range("A1").Offset(Row, 1) , so I also adopted that.

With those revisions, it now runs in about 1/2 of a second(or less) every time I run it: (Some how before it seemed to be clogging up my Ram)

I am going to now work on understanding how I might modify the formula and format code to possibly use Union for Non-contiguous cells, but it runs fast and well as is, so I may not.. If I run into any issues with that I'll just make a new post.

Sub Reset_RowHeight_ColWidths_And_Formulas()

Dim Row As Integer, col As Integer
Dim myRows As Double, myCols As Double

With ActiveSheet
    .Unprotect
    .EnableCalculation = False

    Application.ScreenUpdating = False

    For Row = 1 To 259
    myRows = .Cells(Row, 1).Row
 
Select Case myRows
Case 1
    .Cells(Row, 1).RowHeight = 45.75
Case 2
    .Cells(Row, 1).RowHeight = 96
Case 3
    .Cells(Row, 1).RowHeight = 19.5
Case 4
    .Cells(Row, 1).RowHeight = 21
Case 5
    .Cells(Row, 1).RowHeight = 18.75
Case 6
    .Cells(Row, 1).RowHeight = 23.25
Case 7
    .Cells(Row, 1).RowHeight = 22.5
Case 8
 'continues to case 259

  End Select
Next
 
For col = 1 To 26
    myCols = .Cells(1, col).Column

Select Case myCols
Case 1
    .Cells(1, col).ColumnWidth = 8.57
Case 2
    .Cells(1, col).ColumnWidth = 29.57
Case 3
    .Cells(1, col).ColumnWidth = 10.71
'Continues to case 26

End Select

Next
 
    .Range("i44").NumberFormat = "General"
    .Range("i44").Value = "=$i$4"
    .Range("e108").NumberFormat = "General"
    .Range("e108").Value = "=$i$4"
    .Range("f215").NumberFormat = "General"
    .Range("f215").Value = "=$i$4"
    
    
    .Range("a214").NumberFormat = "General"
    .Range("a214").Formula = "=CONCATENATE(""To: "",R[-203]C[2])"
    .Range("a223").NumberFormat = "General"
    .Range("a223").FormulaR1C1 = "=CONCATENATE(""To: "",R[-212]C[2])"
   
 'and continues with at least another 150 Non-contiguous cells

   .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, userinterfaceonly:=True

   .EnableCalculation = True

End With

Application.ScreenUpdating = True

MsgBox ("Finished resetting formulas, row heights and columns widths")

End Sub

[westconn1]

you can cut your select cases by using multiple criteria
from you code above

Select Case myCols
Case 1
    .Cells(1, col).ColumnWidth = 8.57
Case 2, 3
    .Cells(1, col).ColumnWidth = 8
'Case 3
'    .Cells(1, col).ColumnWidth = 8
'Continues to case 26
End Select

so you could use like
case 2 to 6, 9 to 13, is >21

you can also use case else for any default value
another option is to set the col width for the entire range first, then only change the cols that are different

select case only evaluates conditions until it finds a match so, for speed, order the cases by the expected most common result (does not apply in this example, as each number is only used once)

also in the above code mycols is always made = to col, so it is not required, you can just select case col

[SQLADOman]

Thanks westconn1

As far as eliminating Mycol and Myrow in favor of col and row, that's interesting, I certainly will give that a try a little later today.

---
There are only a very few Row Heights or Columns widths that are the same, but thanks, i will also incorporate your recommendation's with that, as it certainly will be cleaner looking to code it as in you example:
Case 2, 3
.Cells(1, col).ColumnWidth = 8

---
The case else I am using. Only left it out of the abbreviated example.

[SQLADOman]

HI again westconn1,

I modified and tested the code using only Row and col, as you suggested and that worked well. ---Thanks---
For Row = 1 To 432
myRows = .Cells(Row, 1).Row<- Removed
Select Case Row<-Changed from: myRows to: Row in this command

For col = 1 To 26
myCols = .Cells(1, col).Column<- Removed
Select Case col<-Changed from: myCols to: col in this command

To improve that part of the code even further though,
I decided that because "Row" might technically be thought of as a keyword, it's best to avoid it...So I changed Row to: intRow
and to be consistent I also changed col to: intCol

Before you gave me your excellent tips, the revisions I had made and posted, did already solve my speed issue.
- The further developed code that uses some of your recommendations did not speed things up noticeably,
I would need to use a timer to know, as it already is running in under 1/2 of a second

I decided not to adopt your recommendation to combine cases, because I occasionally make code edits to some of the Row height's,
and some times those that had been the same height, may later become different..
(Also for me, because now that i have expanded it to 432 Row Cases, I see it as quicker to visually locate the Case Row in the code, if i do not have them combined.)

As far as what had originally been causing the code to run so slow, perhaps that may have been my use of Range("A1").Offset(Row, 1) , where now I am using .Cells(Row, 1)

If not that, then maybe I had a typo in my original code, that I inadvertently later corrected.

Either way, thanks for your help.

Improved code sample

Dim intCol As Integer, intRow As Integer
'Dim myRows As Double, mycol As Double<-Discontinued the use of these two variables

With ActiveSheet

     .EnableCalculation = False

     Application.ScreenUpdating = False

For intRow = 1 To 432
 
Select Case intRow

Case 1
    .Cells(intRow, 1).RowHeight = 45.75
Case 2
    .Cells(intRow, 1).RowHeight = 96
Case 3
    .Cells(intRow, 1).RowHeight = 19.5
'and so on -----------------------
Case 432
    .Cells(intRow, 1).RowHeight = 3
End Select

Next

For intCol = 1 To 26
    
Select Case intCol

Case 1
    .Cells(1, intCol).ColumnWidth = 8.57
Case 2
    .Cells(1, intCol).ColumnWidth = 8
Case 3
    .Cells(1, intCol).ColumnWidth = 29.57
'and so on -----------------------
Case 12
    .Cells(1, intCol).ColumnWidth = 1.14
Case Else
    .Cells(1, intCol).ColumnWidth = 8.43

End Select

Next

     .EnableCalculation = True

End With

     Application.ScreenUpdating = True