VBA: Is there a way to rapidly set Font Size property for a large range of cells.

[TomCon]

The code below illustrates that you can set the value of a range of cells to values contained in a same-sized array with one VBA statement and it is fast:
Range("A1:B10000") = aKey

But, the same cannot be used to set the Font Size property. The following statement gives an error:
Range("A1:B10000").Font.Size = aKey

Setting the Font Size cell by cell in a loop takes 71 times longer than the single statement above to set the cell value.

Is there anything analogous to the statement Range("A1:B10000") = aKey to set Font Size (or other cell format properties)?

Thanks.

Code

Sub qwerty()
Dim aKey(1 To 10000, 1 To 2) As Variant

For i = 1 To 10000
    aKey(i, 1) = Math.Round(Rnd * 10) + 8
    aKey(i, 2) = Math.Round(Rnd * 10) + 8
    
Next i

Debug.Print 1, [now()]
Range("A1:B10000") = aKey
Debug.Print 2, [now()]

'DOES NOT WORK - Error "unable to set the Font property"
'Range("A1:B10000").Font.Size = aKey

'Takes 71 times longer than above.
For i = 1 To 10000
    Range("A1").Offset(i - 1, 0).Font.Size = aKey(i, 1)
    Range("A1").Offset(i - 1, 1).Font.Size = aKey(i, 2)
    
Next i
Debug.Print 3, [now()]

End Sub

 

[Fluff]

Not that I'm aware of, when you are trying to give the cells different properties.

 

[Fazza]

Sorting the data might help - then fewer, larger ranges can be addressed. Resort to original order if required. There may be other approaches too.

To be able to give a better answer, can you explain more specifics of the situation/setup/requirement?

 

[TomCon]

I want to duplicate a workbook. I fear the workbook has "lurking corruption". This workbook started probably over 10 years ago as an xls, is now xlsm. Its fairly large (10.5MB) and utilizes many features: charts, tables, PivotTables, Addins, UDFs, Macros, Conditional formats. It is increasingly often "doing odd things". Excel crashes more often than it used to. Occasionally upon Open suddenly there are many many cells that got #REF in them. This i have fixed with open & repair. Sometimes upon Open my chart sheets were renamed to "Chart1", "Chart2", etc (but they had different names in the previous save). But, these issues are not regular and reproducible, they just pop up now and then, but seemingly to me more often than they used to. So, i am "nervous" about this book.

I tried a few other simple suggestions that were suggested on this board, but they have not seemed to fix these intermittent issues.

So i want to loop through the book and create a new book, formula by formula (as text and paste the formula text into the new book) and property by property. Not just "copy" as i am afraid that it might copy some corruption, say.

The copy of formulas is greatly sped up with the single statement illustrated above. So, i was searching for an analogous way to speed up the application of all the cell properties, too. But, if i have to do it cell by cell in a loop, i can just leave the process to run for a few hours if necessary; therefore it is not a disaster, but was searching for a more efficient way if it exists.

Thanks!

 

[Peter_SSs]

For me, with 2 columns and 10,000 rows, this took about 1/10 of the time your 'slow' code took.

Sub qwerty_v2()
  Dim aKey(1 To 10000, 1 To 2) As Long
  Dim i As Long

  For i = 1 To UBound(aKey)
      aKey(i, 1) = Math.Round(Rnd * 10) + 8
      aKey(i, 2) = Math.Round(Rnd * 10) + 8
  Next i
  
  Application.ScreenUpdating = False
  With Range("K1:L1").Resize(UBound(aKey))  '<- Choose 2 vacant columns
    .Value = aKey
    On Error Resume Next
    For i = 8 To 18 '<- Possible font sizes
      .Replace What:=i, Replacement:="#N/A", LookAt:=xlWhole
      .SpecialCells(xlConstants, xlErrors).Offset(, -10).Font.Size = i '<- Change offset if not using columns K:L
      .SpecialCells(xlConstants, xlErrors).ClearContents
    Next i
    On Error GoTo 0
  End With
  Application.ScreenUpdating = True
End Sub

 

[CephasOz]

Perhaps, if most of the cells are of a given font size, set all of the copied cells to that size, then in your loop, you just have to set the font size for those that aren't that size. (Hopefully, no cell has different sizes for different characters.)

And the code that @Peter_SSs posted raises a good point - the more you can qualify objects, the faster your code will run (and the fewer bugs will arise). And using the "With" statement is part of that - it is a "big gun" in writing code that is faster, easier to understand, and easier to revise.

 

[TomCon]

These are great ideas...thanks! As you can see by my having defined it...its a big task. I'm working on the pieces piece by piece than plan to put it all together to run and generate the new [hopefully clean] copy of the workbook. I'll try out that code, and the idea of "more qualification" for better efficiency. Thanks very much!

 

[Fazza]

Thanks Tom for the excellent explanation, I guess you know already that the complexity of the many different elements in a very large spreadsheet risks problems - and so as much as possible reducing that complexity is good practice. In spreadsheets with problems the biggest issue I see is in defined names. Some spreadsheets are copied from project to project and many defined names that are unneeded ended up in the file - sometimes hidden - and often with errors. Via the name manager (CTRL-F3) these can generally be easily reviewed & deleted. VBA to unhide of course. Other common issues are excessive used ranges, formatting where not needed and in Excel since 2007 (not all versions?) very large ranges with conditional formatting. If there is a lot of conditional formatting, consider having VBA do the conditional formatting as required - and remove when not required: such as worksheet de/activation. If many different fonts & formats are needed, well so be it, but if not try to keep it simple. All the best