代码之家 › 专栏 › 技术社区 › Alex

如果第2行的内容不等于“x”、“y”或“z”,则隐藏列

vba excel

0

Alex · 技术社区 · 7 年前

我必须查看在不同位置具有列的多个工作表(例如:工作表1名称列为A,工作表2名称列为B)。

编辑:根据@ComradeMicha的评论,我把一些东西放在一起,我肯定是错的,但我怎样才能让它起作用呢?

Sub Demo()

Dim arr(2) As String
Dim rng As Range: Set rng = Application.Range("Data!A2:CA2")
Dim cel As Range

arr(0) = "Name"
arr(1) = "Age"
arr(2) = "Gender"

For Each cel In rng.Cells
    With cel
        If Not IsInArray(cell, arr) Then
            Columns(cel).Hidden = True
        End If
    End With
Next cel
End Sub


Private Function IsInArray(valToBeFound As Variant, arr As Variant) As Boolean
'DEVELOPER: Ryan Wells (wellsr.com)
'DESCRIPTION: Function to check if a value is in an array of values
'INPUT: Pass the function a value to search for and an array of values of any data type.
'OUTPUT: True if is in array, false otherwise
Dim element As Variant
On Error GoTo IsInArrayError: 'array is empty
    For Each element In arr
        If element = valToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next element
Exit Function
IsInArrayError:
On Error GoTo 0
IsInArray = False
End Function

2 回复 | 直到 6 年前

1

urdearboy 7 年前

如果你只是在寻找这三个关键词的话,这应该可以做到。它不像检查数组那么快,但是您没有在那么多列中循环,所以这里的差别将非常小。

Option Compare Text 删除文本比较的区分大小写方面。换句话说,宏将假定 NAME = name
没有这个 Option 姓名 <gt; 名称

Option Explicit
Option Compare Text

Sub HideColumns()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Data")
Dim MyCell As Range
Dim HideMe As Range

Application.ScreenUpdating = False
    For Each MyCell In ws.Range("A2:CA2")
        If MyCell = "Name" Or MyCell = "Age" Or MyCell = "Gender" Then
            If HideMe Is Nothing Then
                Set HideMe = MyCell
            Else
                Set HideMe = Union(HideMe, MyCell)
            End If
        End If
    Next MyCell

    If Not HideMe Is Nothing Then
        HideMe.EntireColumn.Hidden = True
    End If
Application.ScreenUpdating = True

End Sub

2

0

ComradeMicha 7 年前

是的,需要VBA根据值自动隐藏列。

循环浏览第2行中的每一列(例如,使用: https://www.excel-easy.com/vba/examples/loop-through-defined-range.html ),检查单元格值是否在白名单中(例如,使用此: https://wellsr.com/vba/2016/excel/check-if-value-is-in-array-vba/ ),如果不是,则隐藏列:

Columns(i).hidden = true