Excel VBA loop and show interactively changes

Question

I have a VBA Application which loops through a range and does some changes. However, I don't care much about the running time but I want to show the User that changes were made in run time.

I was thinking about something like this.

Private Sub ProcessALBODY(ByRef Fullpath, ByRef MasterWB)
...

    For Each Row In rng.rows
    ... 'Do Something

    DoEvents
    ActiveWindow.ScrollRow = 1 'for each row scrolldown and proceed to next row
    Next

...
End Sub

Unfortunately my application freezes until it has finished..

@TimWilliams Saved my day sir, please write it as an answer! — V.Hunon
– V.Hunon, Commented May 29, 2019 at 0:47
Hey @V.Hunon what about mine?!? Aside from inducing seizures, it is awesome?!?! ☄😜 — pgSystemTester
– pgSystemTester, Commented May 29, 2019 at 15:50

Tim Williams · Accepted Answer · 2019-05-29 02:27:04Z

2

ActiveWindow.ScrollRow = Row.Row

should do what you want

answered May 29, 2019 at 2:27

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pgSystemTester Over a year ago

Nice. Better than mine.

GarrettS1 · Accepted Answer · 2019-05-29 01:38:07Z

0

You could use the status bar to show progress. Use Application.StatusBar = and then follow it with your choice of string, integer, etc. If it is put inside the 'For' statement, it would update with each iteration.

answered May 29, 2019 at 1:38

GarrettS1

766 bronze badges

Comments

pgSystemTester · Accepted Answer · 2019-05-29 02:53:25Z

0

What select or hide not work?

rng.EntireRow.Hidden = True
    For Each row In rng.Rows

        row.EntireRow.Hidden = False
        'do something.
        rng.EntireRow.Hidden = True

    Next

rng.EntireRow.Hidden = False

or

For Each row In rng.Rows

    row.Select
    'do something.

Next

answered May 29, 2019 at 2:53

pgSystemTester

10.1k2 gold badges28 silver badges59 bronze badges

Collectives™ on Stack Overflow

Excel VBA loop and show interactively changes

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related