Improve VBA Calculation speed

[Excelquestion35]

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).

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.

 

[Joe4]

See if this helps speed things up:

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 ....
   
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

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
   
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 

End Sub

 

[Georgiboy]

Maybe the below will be quicker:

Sub D2S_Button()
   
    Range("A:CO").EntireColumn.Hidden = False
    Range("H1:CO1").SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = True
    Range("F12").AutoFilter Field:=7, Criteria1:="D2S"
End Sub

 

[Excelquestion35]

Thanks both for the swift reply!

@Joe4, unfortunately I don't see an improvement speed in the calculation (also hiding the columns does not work anymore)
@Georgiboy, your solution is much quicker, but the macro unfortunately does not hide the correct columns anymore

 

[Joe4]

Thanks both for the swift reply!

@Joe4, unfortunately I don't see an improvement speed in the calculation (also hiding the columns does not work anymore)
@Georgiboy, your solution is much quicker, but the macro unfortunately does not hide the correct columns anymore

That makes me question whether or not these columns really are blank.
(Note that I did not change any of the logic of your code. I simply suppressed calculations and screen updates until the end.

Are the values in row 1 hard-coded or the results of formulas?
Note that even a single space is not the same as blank/empty.

 

[Excelquestion35]

It is a bit complicated, but couldn't think of a smarter way: for all departments in checks whether the column is a applicable (= if sum of all rows is at least higher than 0, indicating that at least somebody is somewhat skilled for a particular activity (= column))

Formula in column AC1:

=IF(SUMIF($G$13:$G$1048576;$H1;AC$13:AC$1048576)>0;$H1;"")

Idea is to only show the columns that contain the value of the department in one of the six corresponding top rows

 

[Georgiboy]

Maybe this:

Sub D2S_Button()
    Dim rCell As Range, uRng As Range
  
    Range("A:CO").EntireColumn.Hidden = False
    For Each rCell In Range("H1:CO1").Cells
        If rCell.Value = vbNullString Then
            If uRng Is Nothing Then
                Set uRng = rCell
            Else
                Set uRng = Union(uRng, rCell)
            End If
        End If
    Next rCell
   
    If Not uRng Is Nothing Then
        uRng.EntireColumn.Hidden = True
    End If
    Range("F12").AutoFilter Field:=7, Criteria1:="D2S"
End Sub

It will build a range of all of the columns with a formulae that equates to "", it will then hide all of those columns at once.

 

[Excelquestion35]

This code seems much quicker but for some reason I am not able to get it to work.

It does the filtering in column F correctly.
Yet, it does not hide the columns without the department in the top row.
Am I doing something wrong?

 

[Excelquestion35]

Hi all,

Just wanted to let you know that the macro was not the problem, it was the table decreasing the speed of the document.

Thank you for your input!