Home

Excel Column Management

%3CLINGO-SUB%20id%3D%22lingo-sub-732044%22%20slang%3D%22en-US%22%3EExcel%20Column%20Management%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732044%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20an%20Excel%20guru%2C%20but%20I%20do%20have%20an%20Excel2013%20book%20by%20Microsoft.%26nbsp%3B%20I%20have%20a%20budget%20in%20Excel%20and%20the%20columns%20are%20identified%20by%20month.%26nbsp%3B%20I%20would%20like%20to%20automatically%20hide%20the%20previous%20months%20and%20directly%20focus%20on%20the%20current%20month.%26nbsp%3B%20So%20when%20I%20open%20excel%20the%20next%20month%2C%20the%20only%20column%20I%20see%20is%20that%20month.%26nbsp%3B%20So%20today%20is%20July%201st%2C%20when%20I%20open%20this%20spreadsheet%2C%20I%20should%20not%20see%20the%20previous%20months%20I%20should%20only%20see%20July%2019%20(this%20would%20apply%20to%20any%20days%20within%20July%2019).%26nbsp%3B%20When%20I%20open%20this%20spreadsheet%20in%20Aug%2019%2C%20I%20should%20only%20see%20Aug.%26nbsp%3B%20Thank%20you%20for%20your%20patience%20and%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-732044%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-732932%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Column%20Management%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369457%22%20target%3D%22_blank%22%3E%40Nicarlov%3C%2FA%3E%26nbsp%3BYou%20can%20add%20this%20code%20to%20the%20the%20ThisWorkbook%20module%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EPrivate%20Sub%20Workbook_Open()%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Dim%20strMonth%20As%20String%0A%20%20%20%20%0A%20%20%20%20strMonth%20%3D%20Format(Date%2C%20%22mmmm%22)%0A%20%20%20%20'Debug.Print%20strMonth%0A%20%20%20%20%0A'%20%20%20Unhide%20all%20month%20columns%0A%20%20%20%20Range(%22B1%3AM1%22).EntireColumn.Hidden%20%3D%20False%0A%20%20%20%20%0A'%20%20%20Loop%20through%20each%20column%20-%20If%20it's%20%26lt%3B%26gt%3B%20to%20this%20month%2C%20then%20hide%20it%0A%20%20%20%20For%20i%20%3D%202%20To%2013%0A%20%20%20%20%20%20%20%20If%20Cells(1%2C%20i).Value%20%26lt%3B%26gt%3B%20strMonth%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Cells(1%2C%20i).EntireColumn.Hidden%20%3D%20True%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20i%0AEnd%20Sub%3C%2FPRE%3E%0A%3CP%3EIf%20you're%20not%20familiar%20with%20VBA%20code%2C%20then%20press%20Alt%2BF11%20to%20open%20the%20VB%20Editor.%20Next%2C%20find%20the%20module%20called%20ThisWorkbook%2C%20double-click%20it%2C%20then%20paste%20the%20code%20into%20the%20new%20window%20that%20opens%20on%20the%20right.%20You%20can%20test%20it%20by%20pressing%20F5.%20Alt%2BQ%20will%20exit%20you%20back%20to%20Excel%2C%20and%20the%20code%20will%20run%20each%20time%20you%20open%20the%20workbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHTH%3C%2FP%3E%3C%2FLINGO-BODY%3E
Nicarlov
Occasional Visitor

I am not an Excel guru, but I do have an Excel2013 book by Microsoft.  I have a budget in Excel and the columns are identified by month.  I would like to automatically hide the previous months and directly focus on the current month.  So when I open excel the next month, the only column I see is that month.  So today is July 1st, when I open this spreadsheet, I should not see the previous months I should only see July 19 (this would apply to any days within July 19).  When I open this spreadsheet in Aug 19, I should only see Aug.  Thank you for your patience and help.

1 Reply

@Nicarlov You can add this code to the the ThisWorkbook module:

 

Private Sub Workbook_Open()
    Dim i As Long
    Dim strMonth As String
    
    strMonth = Format(Date, "mmmm")
    'Debug.Print strMonth
    
'   Unhide all month columns
    Range("B1:M1").EntireColumn.Hidden = False
    
'   Loop through each column - If it's <> to this month, then hide it
    For i = 2 To 13
        If Cells(1, i).Value <> strMonth Then
            Cells(1, i).EntireColumn.Hidden = True
        End If
    Next i
End Sub

If you're not familiar with VBA code, then press Alt+F11 to open the VB Editor. Next, find the module called ThisWorkbook, double-click it, then paste the code into the new window that opens on the right. You can test it by pressing F5. Alt+Q will exit you back to Excel, and the code will run each time you open the workbook.

 

HTH

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies