Excel VBA实现：隐藏B9:B1108中不匹配C5值的行（按钮控制）

First, let's clear up where to put your code: you should paste this directly into the worksheet module for "Employee information", not a separate standard module. Here's how:

• Right-click the "Employee information" sheet tab at the bottom of Excel.
• Select "View Code" from the menu.
• The VBA editor will open with the worksheet's code module active. Paste your button click subs here.

Now, let's update your CommandButton1_Click code to hide only rows where column B doesn't match the value in C5. We'll start by unhiding all rows (to reset), then loop through each row to check the condition:

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim targetRow As Long
    Dim matchValue As Variant
    
    ' Set reference to your worksheet
    Set ws = ThisWorkbook.Worksheets("Employee information")
    ' Get the value we want to match from C5
    matchValue = ws.Range("C5").Value
    
    ' First, unhide all rows in your target range to start fresh
    ws.Range("B9:B1108").Rows.Hidden = False
    
    ' Loop through each row from 9 to 1108
    For targetRow = 9 To 1108
        ' Hide the row if column B's value doesn't match C5
        If ws.Cells(targetRow, "B").Value <> matchValue Then
            ws.Rows(targetRow).Hidden = True
        End If
    Next targetRow
End Sub

Your CommandButton2_Click can be updated to also clear any existing filters (in case you switch to using AutoFilter later) and unhide all rows:

Private Sub CommandButton2_Click()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Employee information")
    
    ' Clear any active filters and unhide all rows
    ws.AutoFilterMode = False
    ws.Range("B9:AJ1108").Rows.Hidden = False
End Sub

Quick Notes: #

• Data Type Matching: If column B has text values and C5 has a number (or vice versa), the comparison might fail. To fix this, convert both values to the same type, e.g., CLng(ws.Cells(targetRow, "B").Value) <> CLng(matchValue) (use CStr for text instead of CLng for numbers).
• Performance: For large ranges (like 1000+ rows), using AutoFilter is faster than looping. If you have a header row above row 9 (e.g., row 8), you could replace the loop with an AutoFilter:

  ' Replace the loop with this if you have a header in row 8
  ws.Range("B8:AJ1108").AutoFilter Field:=1, Criteria1:=matchValue
  

Just paste these subs into your worksheet's code module, save your workbook as a .xlsm (macro-enabled) file, and your buttons should work exactly as you want!

内容的提问来源于stack exchange，提问作者DNM