Optimizing VBA macro with Do While

Question

I have an excel file with a LOT of worksheets, and i wanted to run a macro that would hide a range of rows based on the value at the top of this range. My macro works, but since i have a ton or worksheets, it is taking forever to run...

Can somebody help me in optimizing it, because i may have done things unorthox-ly...

Sub MasquerPrix()

Dim RowNum As Long
Dim StartRow As Long
Dim ColNum As Long

Columns("D:H").Select
Range("F1").Activate
Selection.EntireColumn.Hidden = False
Columns("E:F").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
        
StartRow = 1
RowNum = 1
ColNum = 2

    Do While Cells(RowNum, ColNum).Value <> "Prix  Total (Public HT)"
        If Cells(RowNum, ColNum).Value <> "Prix  Total (Public HT)" Then
            Rows(RowNum).Resize(12).EntireRow.Hidden = True
            Rows(StartRow & ":" & (RowNum)).EntireRow.Hidden = False
        End If
        RowNum = RowNum + 1
    Loop
End Sub

Thanks a million !

You can use `Application.Match()` instead `Do ... Loop` for value searching . I also recommend that you pay attention to [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Алексей Р, Jun 23 '21 at 09:55
I tried using Application.Match, but i am new to VBA and i cannot quite figure out how to use an If function after it so it takes the value it matched and format its cell... Do you know anything about that? — Maximilien Ollier, Jun 23 '21 at 11:17
Okay, i did it, thanks a lot, my boss is impressed thanks to you... — Maximilien Ollier, Jun 23 '21 at 11:33

score 0 · Answer 1 · answered Jun 23 '21 at 11:41

Not having the raw data and not understanding the logic of its processing, sketched a version of the procedure using Application.Match() instead of Do ... Loop

Sub MasquerPrix()   'processes ActiveSheet
    Const STR_TO_MATCH = "Prix  Total (Public HT)", COL_NUM = 2
    Dim RowNum As Long, StartRow As Long
    
    Application.ScreenUpdating = False
    Columns("D:H").Hidden = False
    Columns("E:F").Hidden = True
            
    StartRow = 1

    RowNum = Application.Match(STR_TO_MATCH, Columns(COL_NUM), 0)
    If IsNumeric(RowNum) Then
        Rows(RowNum).Resize(12).EntireRow.Hidden = True
        Rows(StartRow & ":" & RowNum).EntireRow.Hidden = False
    End If
    Application.ScreenUpdating = True
End Sub

Now that you are here, would you know if it's possible to CTRL-F and replace command buttons values all at once? I have like a billion of them and i want to change their text without having to modify them one by one... — Maximilien Ollier, Jun 23 '21 at 12:31
@MaximilienOllier I have not heard of such a built-in feature — Алексей Р, Jun 23 '21 at 13:17

Optimizing VBA macro with Do While

1 Answers1