如何根据第一行中的字符串隐藏列并设置宽度

Question

>我有一堆工作簿，第一行有特定的字符串。因此，例如，假设我在 A - D 行中有"狗"、"猫"、"松鼠"和"鱼"。我想要一个宏来搜索值"狗"和"松鼠"，并隐藏列，然后将"猫"和"鱼"的宽度设置为值 11。

我不能只使用列号，因为它们在我的所有工作簿中都不一致。感谢您的帮助。

Sub HideColumn1()
If Range("B4").Value = 0 Then
    Columns("H").EntireColumn.Hidden = True
Else
    Columns("H").EntireColumn.Hidden = False
End If
End Sub

Answer 1

将

范围（"A1：D1"）更改为所需的范围或更动态的范围：

Dim cell As Range
Dim hiddenRng As Range, widthRng As Range
For Each cell In Range("A1:D1")
    If cell = "Dog" Or cell = "Squirrel" Then
        If (hiddenRng Is Nothing) Then
            Set hiddenRng = cell
        Else
            Set hiddenRng = Union(hiddenRng, cell)
        End If
    End If
    If cell = "Cat" Or cell = "Fish" Then
        If (widthRng Is Nothing) Then
            Set widthRng = cell
        Else
            Set widthRng = Union(widthRng, cell)
        End If
    End If
Next cell
If (Not hiddenRng Is Nothing) Then hiddenRng.EntireColumn.Hidden = True
If (Not widthRng Is Nothing) Then widthRng.EntireColumn.ColumnWidth = 11

Answer 2

这是另一个例子

Sub hideColumns()
Dim iRow, iCol As Integer
iRow = 1
iCol = 1
Dim cat, dog, fish, squirrel As String
cat = "Cat"
dog = "Dog"
fish = "Fish"
squirrel = "Squirrel"
Do While Cells(iRow, iCol).Value <> ""
    If Cells(iRow, iCol).Value = dog Or Cells(iRow, iCol).Value = squirrel Then
        Cells(iRow, iCol).Select
        Selection.EntireColumn.Hidden = True
    ElseIf Cells(iRow, iCol).Value = cat Or Cells(iRow, iCol).Value = fish Then
        Cells(iRow, iCol).EntireColumn.Select
        Selection.ColumnWidth = 11
    Else
        'did not find anything do somthing
    End If
iCol = iCol + 1
Loop
End Sub