Show result cleaning sheet

Watersource

New Member
Joined
Jan 3, 2009
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,

I have created a VBA macro that first cleans the content of the sheets and then fills the sheet with new data again. As quite a lot of data is gathered, I have tried to show that cleaning the content has finished (by putting .ScreenUpdating to True and False again) but this does not work yet. No updating takes place during running of this macro, only when it is finished. Could you give me some pointers how to achieve this.

VBA Code:
Sub Refresh_Data()

Dim sht As Worksheet
Dim shtActiveSht As Worksheet

Set shtActiveSht = ActiveSheet

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
On Error Resume Next

ClearSheets --sub to clear contents

.ScreenUpdating = True
.ScreenUpdating = False

On Error GoTo 0
.Run ("EG2000.XLA!ThisWorkbook.AddIn_OnRefresh")

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
MsgBox ("The reports have been updated.")
End With

End Sub

Thanks for your help on this.
 
Last edited:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,871
Office Version
  1. 365
Platform
  1. Windows
May not be able to do exatly what you want. But may be able to offer something to give user comfort
Please post macro ClearSheets
 

Watersource

New Member
Joined
Jan 3, 2009
Messages
13
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
May not be able to do exatly what you want. But may be able to offer something to give user comfort
Please post macro ClearSheets
The ClearSheets macro is the following:
VBA Code:
Sub ClearSheets()

Dim lastrow As Long
Dim sht As Worksheet
Dim shtActiveSht As Worksheet
Set shtActiveSht = ActiveSheet

For Each sht In ActiveWorkbook.Worksheets
    sht.Activate
        Range("A10").Select
        lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Rows("10:" & lastrow).ClearContents
    Next sht
shtActiveSht.Activate

End Sub

Just to be complete, we use Excel 2016.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,447
Messages
5,547,974
Members
410,820
Latest member
Prepost
Top