Slow macro

[country101]

Hi, I have a macro that I would think is pretty simple considering how fast I see it do other tasks. I was wondering if anyone sees anything in this code that would make it do unnecessary steps or run longer than necessary. It's just annoying that it takes a bit to run instead of being almost instant, so I figured I would take the time to see if anyone has any knowledge to impart since most of my macros are copy and paste from ones others post without having the knowledge behind them to know why they are structured a certain way.

The sheet has a set of columns and rows that are frozen to show names and hire dates that always need to be visible. One of these is row 3, which shows the days of the year. This macro is designed to hide columns with formulas in them and then hide any columns outside of the range of date+9 so that, when printed, it will show only days that are applicable and will make it easier to find the dates needed for input.

Thanks for the information.

Sub Hide()
'
' Hide Macro
'
' Keyboard Shortcut: Ctrl+h
'
    Columns("D").Select
    Selection.EntireColumn.Hidden = True
    Columns("NV:NW").Select
    Selection.EntireColumn.Hidden = True
    Dim DateRng As Range
Dim DateCell As Range
Dim WorkSht As Worksheet


Dim cell As Range
For Each cell In Range("E3:NU3")
If cell.Value < Date Or cell.Value > Date + 9 Then
cell.EntireColumn.Hidden = True
End If
Next
For Each WorkSht In Worksheets
    WorkSht.Select
    Set DateRng = Range("3:3")
    For Each DateCell In DateRng
        If DateCell.Value = Date Then DateCell.Select
    Next
Next WorkSht
Worksheets(1).Select
End Sub

 

[Macropod]

I can't see the point of this code:

For Each WorkSht In Worksheets
    WorkSht.Select
    Set DateRng = Range("3:3")
    For Each DateCell In DateRng
        If DateCell.Value = Date Then DateCell.Select
    Next
Next WorkSht

All it does is loop though all the worksheets, selecting them (quite unnecessarily, it seems), before selecting certain cells on row 3 without ever doing anything with them once selected and before moving on to the next cell.

As for your preceding code, the following would be much more efficient:

Application.ScreenUpdating = False
Columns("D").EntireColumn.Hidden = True
Columns("NV:NW").EntireColumn.Hidden = True
Dim cell As Range, Dt As Date
Dt = Date + 9
For Each cell In Range("E3:NU3")
  With cell
    If .Value < Date Then
      .EntireColumn.Hidden = True
    ElseIf .Value > Dt Then
      .EntireColumn.Hidden = True
    End If
  End With
Next
Application.ScreenUpdating = True

 

[country101]

I will try that.

As I have mentioned, I have just copied and pasted macros that others have written and adapted them however I could figure out, so I am sure there are things that could be taken out or done better. Can you explain the screen updating line and what/how/why it is used?

The point of that bit of code is to select the day's date so that when unhidden it would always have the range of cells where data was to be entered on screen. Also, when unhiding, it brings the cells on screen. Is there a simpler way to select today's date?

 

[country101]

I took out the parts about worksheet in the date select portion and it still worked. Doing that and using your code sped things up appreciably. It probably took half the time. I will have to incorporate it into my other macros. I appreciate it. Another 20 years of doing this and I might actually understand it. lol

Sub Hide()
'
' Hide Macro
'
' Keyboard Shortcut: Ctrl+h
'
    Application.ScreenUpdating = False
Columns("D").EntireColumn.Hidden = True
Columns("NV:NW").EntireColumn.Hidden = True
Dim cell As Range, Dt As Date
Dt = Date + 9
For Each cell In Range("E3:NU3")
  With cell
    If .Value < Date Then
      .EntireColumn.Hidden = True
    ElseIf .Value > Dt Then
      .EntireColumn.Hidden = True
    End If
  End With
Next
Application.ScreenUpdating = True


Set DateRng = Range("3:3")
    For Each DateCell In DateRng
    If DateCell.Value = Date Then DateCell.Select
Next
End Sub

 

[My Aswer Is This]

A lot of Vba for me is reasonable easy to read:

For example:

Can you explain the screen updating line and what/how/why it is used?

Application.ScreenUpdating = False

While the script is running a lot of things are happening and you really do not need to see this and displaying all these changes on the screen slows the script down.

So:
Application.ScreenUpdating = False
Means the screen will not show these changes to you.

When the script ends you will see:
Application.ScreenUpdating = True

That means now all the changes that were made you can now see.
And all future changes you make will be seen instantly.

 

[country101]

Thank you. That is what I assumed, but I had never seen that verbiage.

Could you explain the term "dim" and how it is used?

 

[My Aswer Is This]

Maybe you should search the internet and you can find a lot of knowledge about Excel.
I'm not able to explain in detail what every line of code in a script does.
Here is a link that may help explain "Dim"

Excel VBA Variables - EASY Excel Macros