Excelquestion35
Board Regular
- Joined
- Nov 29, 2021
- Messages
- 53
- Office Version
- 2016
- Platform
- Windows
Hello,
I am looking for a way to improve the calculation speed in my workbook. Currently I have six macro buttons doing the same for all six departments (here D2S).
Yet, when running the code, it takes about 30-60 seconds to show the necessary columns in the workbook. Even with 'only' 200 rows in the document.
Would it be wise to change this code into an array? If so, how am I able to do that?
If not, what would be wise to replace/change in order to decrease the processing time?
Unfortunately, I don't have any experience with arrays.
P.s. Don't know if it is important; I already removed a column with 6 Vlookups per cell by VBA code which does the calculation and pastes the outcome as values.
I am looking for a way to improve the calculation speed in my workbook. Currently I have six macro buttons doing the same for all six departments (here D2S).
VBA Code:
Sub D2S_Button()
'Description: This macro will loop through a row and
'hide the column if the cell in row 1 of the column
'has the value of ....
Dim c As Range
Columns("A:CO").Hidden = False 'unhide all columns
Range("F12").AutoFilter Field:=7, Criteria1:="D2S"
For Each c In Range("H1:CO1").Cells
If c.Value = "" Then
c.EntireColumn.Hidden = True
End If
Next c
End Sub
Yet, when running the code, it takes about 30-60 seconds to show the necessary columns in the workbook. Even with 'only' 200 rows in the document.
Would it be wise to change this code into an array? If so, how am I able to do that?
If not, what would be wise to replace/change in order to decrease the processing time?
Unfortunately, I don't have any experience with arrays.
P.s. Don't know if it is important; I already removed a column with 6 Vlookups per cell by VBA code which does the calculation and pastes the outcome as values.