excel - 在excel中隐藏选择之外的列

Question

我有一个简单的 VBA 可以根据下拉列表在 Excel 中选择支付期日期。如何在选定的支付期之前和之后隐藏某个范围内的所有列？

  Private Sub CommandButton1_Click()

Dim ppe_start As Long
Dim ppe_end As Long

ppe_start = Range("B1")
ppe_end = Range("B2")

Columns("C:ER").Hidden = True

Range(Columns(ppe_start), Columns(ppe_end)).Select.Hidden = False

End Sub

使用此代码，我收到运行时错误 424：需要对象。

标签： excelvba

Answer 1

我假设您在下拉列表中设置了日期。

我的示例数据如下所示

B1然后，您将隐藏除日期 () 2018-03-01和 ( B2) 2018-07-01之间的列之外的所有列。

我将您的代码修改为动态的。

VBA代码：

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1") 'set Sheet name

Dim ppe_start As Date
Dim ppe_end As Date
Dim FoundStart As Long
Dim FoundEnd As Long

ppe_start = Range("B1") 'Set first date
ppe_end = Range("B2") 'Set last date

Columns.EntireColumn.Hidden = False 'Unhide all columns

With ws
    FoundStart = .Range("3:3").Find(What:=ppe_start, LookIn:=xlValues, LookAt:=xlWhole).Column - 1 'Find column number where First date exist in row 3
    FoundEnd = .Range("3:3").Find(What:=ppe_end, LookIn:=xlValues, LookAt:=xlWhole).Column + 1 'Find column number where Last date exist in row 3

    .Range(.Cells(3, "C"), .Cells(3, FoundStart)).EntireColumn.Hidden = True 'From Column C until Column First date, hide
    .Range(.Cells(3, FoundEnd), .Cells(3, "ER")).EntireColumn.Hidden = True 'From Column Last Date until Column ER, hide
    .Columns("B").ColumnWidth = 9.71 'To preserv the width of Column B, not necessary
End With
End Sub

结果将是这样的：