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
Highlighted
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
Highlighted

@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
Excel file not opening
mmirfan in Excel on
0 Replies
Excel Add-In - Visio Data Visualiser - Sign in issue
MrWLJ71 in Excel on
0 Replies
Macro Not working
Rajeev_Raghavan in Excel on
3 Replies
Need Help Creating This Formula
James Reuland in Excel on
0 Replies
Excel Find and replace
Brickson_Dimas in Excel on
2 Replies