Is there a more efficient way of doing what is listed in the code below?

Question

Ok so I've googled this and checked on here, but there is nothing similar to what I need looking at and i'm not familiar with VBA, only Python.

In python I would select the data and put it into a list, then do my stuff, then retrieve the data from the list and apply it. What I'm asking is whether there is a way to simplify the below code using arrays; if there is what would be the best way to do that. This code has 5 of these for loops, which I think is where we are losing the efficiency. Currently the macro will take 10 minutes to run once. I have a feeling that is because the for loops here refresh the page for each cell selection? i might be wrong with that. I'm happy to post more code and even the spreadsheet if required. Rally appreciate anyone taking the time to have a look at this!

Set ar = Selection

For Each ar In ar.Rows
    newHeight = ar.RowHeight + 12.5
    ar.VerticalAlignment = xlTop
    ar.RowHeight = newHeight
Next ar

For Each Row1 In Sheets("ReportSummary").Range("4:26").Rows
If Row1.Cells(1, 2).Value = "" Then Row1.RowHeight = 0
Next

Sheets("ReportSummary").Select

Sheets("ReportSummary").Range("F4:F26").WrapText = True
Sheets("ReportSummary").Range("F4:F26").EntireRow.AutoFit

Sheets("ReportSummary").Range("F4:F26").Select`

It basically looks like you're just doing page formatting. This is not a use case for arrays. — BigBen, Dec 09 '19 at 15:27
Selecting things is usually avoidable and is fairly resource intensive, see here on how to get around it: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — Warcupine, Dec 09 '19 at 15:31
Additionally if you use a filter to only show blank rows and then collapse those rows and remove the filter it will be a lot faster than iterating through a sheet. — Warcupine, Dec 09 '19 at 15:32
Thanks for that @Warcupine I will take a look now and see if I can make some changes to this awful macro! — MildCorma, Dec 09 '19 at 15:34
Why don't you hide those rows instead of altering their size? — Dominique, Dec 09 '19 at 15:54
I can't see why it would take so long, its only looping through 22 cells. Your first loop may take longer are you selecting the actual range or a complete column? — Davesexcel, Dec 09 '19 at 16:12
**1)** What is `Selection` in this context? Is it an entire column? If you put a breakpoint on that first for loop what size is `ar.Rows`? **2)** How is this code triggered? Are you running it manually or is there an event that is firing like `Worksheet_Change()`? — JNevill, Dec 09 '19 at 17:20

score 0 · Answer 1 · answered Dec 09 '19 at 17:27

This is one way to do it:

  Sub test()
    'turn off unnecessary stuff
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    On Error GoTo whoops  'if there's an error make sure we turn it all back on again
    Dim ar As Range
    Dim x As Long
    Set ar = Selection

    For x = ar.Row To ar.Row + ar.Rows.Count - 1

        With ar.Rows(x)
             .RowHeight = .RowHeight + 12.5
             .VerticalAlignment = xlTop
        End With

    Next x
    With Sheets("ReportSummary")
          For x = 4 To 26

                  If .Cells(x, 2) = "" Then .Rows(x).RowHeight = 0
          Next x

          With .Range("F4:F26")
               .WrapText = True
               .EntireRow.AutoFit
          End With
    End With

whoops:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
End Sub

Is there a more efficient way of doing what is listed in the code below?

1 Answers1