Efficient formatting of specific cells by looping through...

ChloeLM

New Member
Joined
Apr 12, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Hello everyone

I'm a VBA novice who has muddled through a piece of code and got it working. However it's very long (and slow!) and i'm looking for some advice on how to make it more elegant (and efficient!) in order to speed up my procedure. Am i using an efficient way to loop through the procedure? Is there a more efficient way to refer to the range other than guessing what the maximum range might be (I tried to incorporate Range("A4:A4").End(xlDown) unsuccessfully)? Is there an optimal order to ask it to do things in which speeds things up?

I have a financial profit and loss spreadsheet where column A contains row headings/sub headings, and column B:E contains corresponding numbers - one financial year in each column. It is normally between 120 and 500 rows long. The spreadsheet is a CSV which is exported from another system, some of the layout is predictable, some is different each time. In particular the main row headings/totals have predictable text, while the rows in between are variable. I need to format the predictable rows.

E.g. In column A there are 64 possible main row headings that might appear (they are not all used each time). These are predictable and I have defined them as constants in the declarations section e.g. Const myLabel7="Staff Costs" and so on for all 64. I then have subheadings under these that have numbers adjacent to them, and where the text in column A is always different. For example, underneath Staff Costs I could have any number of variations - 'paid labour', 'salaries' all of which do have corresponding number amounts. There are also 64 possible 'total' headings that might appear, which correspond to the main row headings e.g. 'Staff Costs Total'. Again i've defined them as constants in the declarations section e.g. Const myLabelTotal7="Staff Costs Total"

In the first draft of my VBA programme I have managed to get it to format the rows in the way that I want but it's slow. I have used the code below (which shows the first 2 blocks, there are another 126 blocks like this), and it is looking for particular text, deciding what type of heading it is based on whether there are adjacent numbers, and formatting it accordingly (i have 5 different versions of the row format e.g. myHeight, myHeight1, myHeight2 - and corresponding myTint, myItalics etc):

VBA Code:
Sub FormatHeadingsMyLabels()


'Looks for text and highlights the entire row.

Range("A1").Activate

             
      For Each cell In Range("A4:A500")
  '***VARIABLE1***
    If InStr((cell.Value), myLabel1) <> 0 Then    'if cell name *contains* then do something
  '***VARIABLE1***
    cell.Offset(0, 5).Activate                          'go 5 cells to the right
    Select Case IsEmpty(ActiveCell)                     'is this cell empty?
        Case False                                      'no   [ignore?]
        Case True                                       'yes - the do the thing on the next line
        ActiveCell.Offset(0, 1).Activate              'activate the next cell along
        ActiveCell = 1                                'and put 1 in it
        ActiveCell.EntireRow.Select
    Selection.Font.Italic = myitalics                  'make it italic
         Selection.RowHeight = myHeight                 'change the row height
         Selection.Font.Bold = myBold                   'make the row bold
             With Selection.Font                         'change the row font and colour
              .ThemeColor = mycolour
              .TintAndShade = mytint
              .Size = mySize
            End With
            With Selection
            .AddIndent = True
            .IndentLevel = myindent
            End With
        End Select
                                            
    End If
    Next
   
    For Each cell In Range("A4:A500")
  '***VARIABLE2***
    If InStr((cell.Value), myLabel2) <> 0 Then    'if cell name *contains* then do something
  '***VARIABLE2***
    cell.Offset(0, 5).Activate                          'go 5 cells to the right
    Select Case IsEmpty(ActiveCell)                     'is this cell empty?
        Case False                                      'no   [ignore?]
        Case True
        ActiveCell.Offset(0, 1).Activate              'activate the next cell along
        ActiveCell = 1                                'and put 1 in it
                                                        'yes - the do the thing on the next line
        ActiveCell.EntireRow.Select
    Selection.Font.Italic = myitalics                  'make it italic
         Selection.RowHeight = myHeight                 'change the row height
         Selection.Font.Bold = myBold                   'make the row bold
             With Selection.Font                         'change the row font and colour
              .ThemeColor = mycolour
              .TintAndShade = mytint
              .Size = mySize
            End With
             With Selection
            .AddIndent = True
            .IndentLevel = myindent
            End With
        End Select
                                            
    End If
    Next

All advice appreciated!

Cheers
Chloe
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,838
Office Version
  1. 365
Platform
  1. Windows
You could do something like this
VBA Code:
Sub FormatHeadingsMyLabels()
   
   For Each cell In Range("A4:A500")
      '***VARIABLE1***
      If InStr(1, cell.Value, myLabel1) > 0 Or InStr(1, cell.Value, myLabel2) > 0 Then  'if cell name *contains* then do something
         If cell.Offset(0, 5).Value = "" Then                        'go 5 cells to the right
            cell.Offset(0, 6).Value = 1            'activate the next cell along
            With cell.EntireRow
               .RowHeight = myHeight                 'change the row height
               With .Font                         'change the row font and colour
                  .Italic = myitalics
                  .Bold = mybold
                  .ThemeColor = mycolour
                  .TintAndShade = mytint
                  .Size = mySize
               End With
               .AddIndent = True
               .IndentLevel = myindent
            End With
         End If
      ElseIf InStr(1, cell.Value, myLabel3) > 0 Then
         If cell.Offset(0, 5).Value = "" Then                        'go 5 cells to the right
            cell.Offset(0, 6).Value = 1            'activate the next cell along
            With cell.EntireRow
               .RowHeight = myHeight2                 'change the row height
               With .Font                         'change the row font and colour
                  .Italic = myitalics2
                  .Bold = mybold
                  .ThemeColor = mycolour2
                  .TintAndShade = mytint2
                  .Size = mySize2
               End With
               .AddIndent = True
               .IndentLevel = myindent2
            End With
         End If
      End If
   Next
End Sub
 

ChloeLM

New Member
Joined
Apr 12, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
Thanks Fluff. I sort of follow (maybe!). Are you meaning that I would use
VBA Code:
If InStr(1, cell.Value, myLabel1) > 0 Or InStr(1, cell.Value, myLabel2) >
as a really long list, so basically list all the headings that would use formatting type 1?

This would mean that instead of having 64 blocks of code each searching for a specific heading and applying the appropriate formatting, i'd have 5 blocks of code - one for each type of formatting - with the headings being searched for all listed as 'or'?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I'd suggest you set up a table of the header values, with columns for the corresponding format options. You can then load the entire table into an array, and simply iterate that array to find the header and then use the related formatting options. Alternatively, reverse the logic, and loop through the array of known headers, find them in column A if available, and apply the relevant formatting.
 

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60

ADVERTISEMENT

While there is certainly lots of stuff you can do to make your code more elegant, I think one of the main things you can do to increase the speed of your macro is to turn off unnecessary calculations and animations while the macro is running.

I often put something like

VBA Code:
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.Calculation = xlCalculationManual

At the beginning of my code, and then turn these settings back on at the end

VBA Code:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.Calculation = xlCalculationAutomatic

If you depend on calculations in the worksheet being performed for your code to work correctly, you have to tell it to update on its own though, so you need to know what the different settings do and know when it's safe to turn them off or on.

In addition there are several other things you can do, such as avoiding using select / activate, or loading your data into an array so you don't have to work on the worksheet, as shown in the other answers here.

For more details, I'd recommend just googling for how to speed up VBA code, there are several good resources out there for how to improve the macro's speed. One example which mentions several of the methods shown in this thread is here.

Good luck with your projects!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,838
Office Version
  1. 365
Platform
  1. Windows
Are you meaning that I would use as a really long list, so basically list all the headings that would use formatting type 1?
I was, but if you have 64 different labels with the same formatting, I'd suggest using an array as Rory suggested.
 

ChloeLM

New Member
Joined
Apr 12, 2021
Messages
5
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
  2. Mobile
  3. Web
I'd suggest you set up a table of the header values, with columns for the corresponding format options. You can then load the entire table into an array, and simply iterate that array to find the header and then use the related formatting options. Alternatively, reverse the logic, and loop through the array of known headers, find them in column A if available, and apply the relevant formatting.
Thanks, will have a go at this :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,102
Messages
5,640,112
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top