Question

excel 中使用vba实现列的筛选

表格第一行内有M，N两个字母，如何使用VBA实现筛选？
按钮一只显示第一行内含有M的列，按钮二只显示第一行内含有N的列，按钮三显示全部

Answer 1

一般采用倒序的方式：例如，将D列中为“否”的整行删除：
Sub test()
For i = Cells(Rows.Count, 4).End(xltoup).Row To 1
If Range("D" & i) = "否" Then
Range("D" & i).EntireRow.Delete
End If
Next
End Sub

Answer 2

下面是3个宏的代码,分别绑定到3个按钮上

Sub combutton1()
Dim a As Range
Range("A1:iv1").EntireColumn.Hidden = False
For Each a In Range("A1:iv1")
If a = "M" Or a = "m" Then a.EntireColumn.Hidden = True
Next
End Sub

Sub combutton2()
Dim a As Range
Range("A1:iv1").EntireColumn.Hidden = False
For Each a In Range("A1:iv1")
If a = "N" Or a = "n" Then a.EntireColumn.Hidden = True
Next
End Sub

Sub combtton3()
Dim a As Range
endcol = Range("iv1").End(xlToLeft).Column
Range("A1:iv1").EntireColumn.Hidden = False
End Sub

追问

谢谢！还有点小问题
endcol = Range("iv1").End(xlToLeft).Column
这句是干什么的啊

Answer 3

录制三个宏就行了。

Answer 4

Sub xianshiM()
Dim EndC As Integer, i As Integer
Columns.Hidden = False
EndC = Range("A1").End(xlToRight).Column
For i = 1 To EndC
If InStr(Cells(1, i), "M") > 0 Then
Columns(i).Hidden = False
Else
Columns(i).Hidden = True
End If
Next
End Sub
Sub xianshiN()
Dim EndC As Integer, i As Integer
Columns.Hidden = False
EndC = Range("A1").End(xlToRight).Column
For i = 1 To EndC
If InStr(Cells(1, i), "N") > 0 Then
Columns(i).Hidden = False
Else
Columns(i).Hidden = True
End If
Next
End Sub
Sub xianshquanbu()
Columns.Hidden = False
End Sub