Speed up macros w/ for each and lots of hidden sheets

gmbristow91

New Member
Joined
Feb 2, 2016
Messages
41
Hi, I have a workbook that has a lot of hidden sheets. I have a macro that copies all the data off of a hidden sheet onto the main worksheet and uses a "for each" function to hide the rows and columns on the main worksheet based on which rows and columns were hidden on the sheet the data was being copied from. My issue is that the more sheets there are in the worksheet, the longer this task takes. It's taking up to 30 seconds now.

Code:
Private Sub RecallBtn_Click()
Dim rownum As Long
Dim colnum As Long
Dim Slctd As String
Dim Wstrgt As Worksheet
Dim wsdtbase As Worksheet
Dim Trgtrnge As Range
Dim Dtbasernge As Range
Set Dtbasernge = Sheets(TMPNames.Value).Range("a1:em200")
Set Trgtrnge = ActiveSheet.Range("a1:em200")
rownum = 1
colnum = 1
Application.ScreenUpdating = False
Application.EnableEvents = False
Trgtrnge.Formula = Dtbasernge.Formula
ActiveSheet.Range("r6").Value = TMPNames.Value


For Each Column In Sheets(TMPNames.Value).UsedRange.Columns
If Sheets(TMPNames.Value).Columns(colnum).Hidden = True Then
ActiveSheet.Columns(colnum).Hidden = True
Else
ActiveSheet.Columns(colnum).Hidden = False
End If
colnum = colnum + 1
Next Column


For Each Row In Sheets(TMPNames.Value).UsedRange.Rows
If Sheets(TMPNames.Value).Rows(rownum).Hidden = True Then
ActiveSheet.Rows(rownum).Hidden = True
Else
ActiveSheet.Rows(rownum).Hidden = False
End If
rownum = rownum + 1
Next Row


Call ProtectSheets
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Caleeco

Well-known Member
Joined
Jan 9, 2016
Messages
966
Hello Gmbristow,

Try turning off calculation mode while the code is running. Updated code below
Code:
Private Sub RecallBtn_Click()
Dim rownum As Long
Dim colnum As Long
Dim Slctd As String
Dim Wstrgt As Worksheet
Dim wsdtbase As Worksheet
Dim Trgtrnge As Range
Dim Dtbasernge As Range
Set Dtbasernge = Sheets(TMPNames.Value).Range("a1:em200")
Set Trgtrnge = ActiveSheet.Range("a1:em200")
rownum = 1
colnum = 1
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With


Trgtrnge.Formula = Dtbasernge.Formula
ActiveSheet.Range("r6").Value = TMPNames.Value




For Each Column In Sheets(TMPNames.Value).UsedRange.Columns
If Sheets(TMPNames.Value).Columns(colnum).Hidden = True Then
ActiveSheet.Columns(colnum).Hidden = True
Else
ActiveSheet.Columns(colnum).Hidden = False
End If
colnum = colnum + 1
Next Column




For Each Row In Sheets(TMPNames.Value).UsedRange.Rows
If Sheets(TMPNames.Value).Rows(rownum).Hidden = True Then
ActiveSheet.Rows(rownum).Hidden = True
Else
ActiveSheet.Rows(rownum).Hidden = False
End If
rownum = rownum + 1
Next Row




Call ProtectSheets
With Application
    .Calculation = xlAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With


End Sub

Let me know how you get on
Thanks
Caleeco
 

gmbristow91

New Member
Joined
Feb 2, 2016
Messages
41
Hello Gmbristow,

Try turning off calculation mode while the code is running. Updated code below
Code:
Private Sub RecallBtn_Click()
Dim rownum As Long
Dim colnum As Long
Dim Slctd As String
Dim Wstrgt As Worksheet
Dim wsdtbase As Worksheet
Dim Trgtrnge As Range
Dim Dtbasernge As Range
Set Dtbasernge = Sheets(TMPNames.Value).Range("a1:em200")
Set Trgtrnge = ActiveSheet.Range("a1:em200")
rownum = 1
colnum = 1
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With


Trgtrnge.Formula = Dtbasernge.Formula
ActiveSheet.Range("r6").Value = TMPNames.Value




For Each Column In Sheets(TMPNames.Value).UsedRange.Columns
If Sheets(TMPNames.Value).Columns(colnum).Hidden = True Then
ActiveSheet.Columns(colnum).Hidden = True
Else
ActiveSheet.Columns(colnum).Hidden = False
End If
colnum = colnum + 1
Next Column




For Each Row In Sheets(TMPNames.Value).UsedRange.Rows
If Sheets(TMPNames.Value).Rows(rownum).Hidden = True Then
ActiveSheet.Rows(rownum).Hidden = True
Else
ActiveSheet.Rows(rownum).Hidden = False
End If
rownum = rownum + 1
Next Row




Call ProtectSheets
With Application
    .Calculation = xlAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With


End Sub

Let me know how you get on
Thanks
Caleeco

Thank you Caleeco! This works well but not perfect. But I'm at around 2 seconds now instead of 20-30 seconds. I'm only going to add more hidden sheets so we will see in the future. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,502
Messages
5,636,696
Members
416,935
Latest member
Atulcp

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