根据用户选择隐藏Excel列

Question

我在单元格B2中有一个验证列表，它从ROW 5(G5:BF5(获得一个列表。

我希望用户能够从验证列表中选择一个值，这将导致从G到BF的所有列都被隐藏，除了与单元格B2中的值匹配的列标题。

我使用了下面的代码，但不断出现应用程序定义或对象定义错误。

我正在使用以下VBA：

工作表>>更改

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Dim the_selection As String
Dim week_in_review As String
the_selection = Worksheets("SortHeatMap").Range("B2")
Dim rep As Integer
For rep = 8 To 25
the_column = GetColumnLetter_ByInteger(rep)
week_in_review = Worksheets("SortHeatMap").Range(column_letter & "5")
If the_selection = week_in_review Then
Worksheets("SortHeatMap").Range(the_column & ":" & the_column).EntireColumn.Hidden = False
Else
Worksheets("SortHeatMap").Range(the_column & ":" & the_column).EntireColumn.Hidden = True
End If
Next rep
End If
End Sub

这在模块1中的(常规(>>GetColumnLetter_ByInteger中。

Public Function GetColumnLetter_ByInteger(what_number As Integer) As String
'This section obtains Column letter of selected week.
GetColumnLetter_ByInteger = ""
If MyColumn_integer <= 26 Then
column_letter = Chr(64 + MyColumn_integer)
End If
If MyColumn_integer > 26 Then
column_letter = Chr(Int((MyColumn_integer - 1) / 26) + 64) & Chr(((MyColumn_integer - 1) Mod 26) + 65)
End If
GetColumnLetter_ByInteger = column_letter
End Function

Answer 1

应该这样做：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rep As Long, v
If Target.Address = Me.Range("B2").Address Then
v = Target.Value
For rep = 8 To 25
Me.Columns(rep).Hidden = (Me.Cells(5, rep).Value <> v)
Next rep
End If
End Sub

当您在工作表代码模块中时，可以使用Me引用工作表。

将列编号转换为字母是一件痛苦的事，你从来没有真正需要这样做。

Answer 2

此代码将首先隐藏指定范围内的所有列，然后显示您在B2中指定的列。这个系统的优点是，之前显示的列将被隐藏。

Private Sub Worksheet_Change(ByVal Target As Range)
Const TriggerCell As String = "B2"
Const FirstColumnToHide As String = "G"
Const LastColumnToHide = "BF"
Dim Rng As Range
Dim ClmToShow As Variant
With Target
If .Address = Range(TriggerCell).Address Then
Application.ScreenUpdating = False
ClmToShow = .Value
On Error Resume Next
ClmToShow = Columns(ClmToShow).Column
If ClmToShow < Columns(FirstColumnToHide).Column Or _
ClmToShow > Columns(LastColumnToHide).Column Then Exit Sub
' if TriggerCell doesn't contain a qualified column reference
' this would also be the case if an error occurred at this point
On Error GoTo 0
Set Rng = Range(Columns(FirstColumnToHide), Columns(LastColumnToHide))
Rng.Columns.Hidden = True
Columns(ClmToShow).Hidden = False
Application.ScreenUpdating = True
End If
End With
End Sub

只要实现起来可行，就要避免在代码中穿插常量。这里很容易做到。所有常量都在代码的顶部声明。如果将来有更改，可以在那里进行，而不必触及代码本身。