Excel按日期隐藏列：创建按钮实现30/60天期限列隐藏

Got it, let's walk through exactly how to set this up in Excel—no prior VBA experience needed, I'll break it down step by step.

Step 1: Enable the Developer Tab (if it's missing) #

• Right-click anywhere on the Excel ribbon > Select Customize the Ribbon
• Check the box next to Developer in the right-hand list > Click OK

Step 2: Insert a Button and Link It to a Macro #

• Go to the Developer tab > Click Insert > Pick the Button (Form Control) under the Form Controls section
• Draw the button on your worksheet (place it somewhere easy to access, like above your data table)
• When the "Assign Macro" window pops up, click New—this opens the VBA Editor where we'll write our code

Step 3: VBA Code Examples for Different Cutoff Days #

Below are ready-to-use code snippets for 30-day, 60-day, and even a dynamic custom cutoff option. Just replace the default code in the VBA Editor with these.

Example 1: Button to Hide Columns Older Than 30 Days #

Sub HideColumnsOver30Days()
    Dim ws As Worksheet
    Dim col As Range
    Dim dateHeader As Range
    Dim cutoffDate As Date
    
    ' Calculate cutoff: Today minus 30 days
    cutoffDate = DateAdd("d", -30, Date)
    
    ' Replace "Sheet1" with your actual worksheet name
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Assume your expected return dates are in row 1 (adjust this row number if needed)
    For Each col In ws.UsedRange.Columns
        Set dateHeader = ws.Cells(1, col.Column)
        
        ' Only check cells that contain valid dates
        If IsDate(dateHeader.Value) Then
            ' Hide the column if the date is older than our cutoff
            col.Hidden = (dateHeader.Value < cutoffDate)
        End If
    Next col
End Sub

Example 2: Button to Hide Columns Older Than 60 Days #

Copy the code above, rename the subroutine, and tweak the cutoff line:

Sub HideColumnsOver60Days()
    Dim ws As Worksheet
    Dim col As Range
    Dim dateHeader As Range
    Dim cutoffDate As Date
    
    cutoffDate = DateAdd("d", -60, Date)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    For Each col In ws.UsedRange.Columns
        Set dateHeader = ws.Cells(1, col.Column)
        If IsDate(dateHeader.Value) Then
            col.Hidden = (dateHeader.Value < cutoffDate)
        End If
    Next col
End Sub

Bonus: Dynamic Button (Input Any Number of Days) #

Want a single button that lets you pick the cutoff each time? Use this:

Sub HideColumnsByCustomDays()
    Dim ws As Worksheet
    Dim col As Range
    Dim dateHeader As Range
    Dim cutoffDate As Date
    Dim daysInput As Variant
    
    ' Ask user for cutoff days
    daysInput = InputBox("Enter the number of days to keep (columns older than this will be hidden):", "Set Cutoff")
    
    ' Validate input is a number
    If Not IsNumeric(daysInput) Then
        MsgBox "Please enter a valid number.", vbExclamation
        Exit Sub
    End If
    
    cutoffDate = DateAdd("d", -daysInput, Date)
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    For Each col In ws.UsedRange.Columns
        Set dateHeader = ws.Cells(1, col.Column)
        If IsDate(dateHeader.Value) Then
            col.Hidden = (dateHeader.Value < cutoffDate)
        End If
    Next col
End Sub

Step 4: Tweak the Button and Sheet Settings #

• Rename Buttons: Right-click the button > Edit Text, and name it something clear like "Hide >30 Days" or "Custom Cutoff"
• Adjust for Your Sheet:
  • Replace "Sheet1" with your actual worksheet name (e.g., "Portfolio Data")
  • If your dates are in row 5 instead of row 1, change ws.Cells(1, col.Column) to ws.Cells(5, col.Column)
• Add an Unhide Button: For convenience, create a second button with this simple macro to show all columns again:

  Sub UnhideAllColumns()
      ThisWorkbook.Worksheets("Sheet1").Columns.Hidden = False
  End Sub
  

That's all! Click the buttons to test—they’ll automatically hide any columns where the header date falls outside your chosen cutoff window.

内容的提问来源于stack exchange，提问作者Joe