Excel VBA隐藏指定行求助：Name为Sam且Ticket为ACCELA

Hey there! Let's get that VBA issue sorted out for you—hiding rows where Name is Sam and Ticket is ACCELA shouldn't be a headache. I'll walk you through a reliable solution, break down why your original code might have failed, and give you steps to get it working.

This loop-based approach is straightforward and avoids common pitfalls, especially if you have a dataset where you need precise control over which rows to hide:

Sub HideSamAccelaRows()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set your target worksheet (replace "YourSheetName" with your actual sheet name if needed)
    Set ws = ThisWorkbook.ActiveSheet
    ' OR use this to target a specific sheet:
    ' Set ws = ThisWorkbook.Sheets("YourSheetName")
    
    ' Find the last row with data in the Name column (column B)
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' Speed up the macro by turning off screen updates
    Application.ScreenUpdating = False
    
    ' Loop from bottom to top (critical to avoid skipping rows when hiding)
    For i = lastRow To 2 Step -1
        ' Check if current row matches both conditions
        If ws.Cells(i, "B").Value = "Sam" And ws.Cells(i, "C").Value = "ACCELA" Then
            ws.Rows(i).Hidden = True
        End If
    Next i
    
    ' Turn screen updates back on
    Application.ScreenUpdating = True
    
    ' Optional: Confirmation message
    MsgBox "Rows where Name = Sam and Ticket = ACCELA have been hidden!", vbInformation
End Sub

• Worksheet Targeting: Always specify which worksheet you're working on—if you skip this, the macro might run on the wrong sheet accidentally.
• Bottom-Up Loop: We loop from the last row up to row 2 (assuming your headers are in row 1) because hiding a row shifts the rows above it down. Looping top-to-bottom would cause you to skip rows that move up into the position you just checked.
• Column References: This code assumes Name is in column B and Ticket is in column C. Adjust the letters ("B", "C") if your columns are in different positions.
• Case Insensitivity: By default, VBA compares strings without case sensitivity, so "sam" or "SAM" would also match. If you need strict case matching, use StrComp(ws.Cells(i, "B").Value, "Sam", vbBinaryCompare) = 0 instead of the basic equality check.

Here are the most common issues that break this type of macro:

• Top-to-Bottom Looping: As mentioned earlier, this causes row number shifting and skipped rows.
• Unspecified Worksheet: No clear reference to the target sheet leads to unexpected behavior.
• Incorrect Column/Row References: Maybe you used the wrong column index (e.g., column 3 instead of 2 for Name) or started looping at the wrong row (like row 1 instead of row 2, including the header).
• Filter Misconfiguration: If you tried using AutoFilter, it's easy to mix up criteria for showing vs hiding rows. The loop method is more intuitive for this exact "hide matches" scenario.

1. Open your Excel file and press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer pane > Insert > Module.
3. Paste the code into the new module window.
4. Adjust the worksheet name and column references if needed.
5. Press F5 to run the macro, or assign it to a button in your Excel sheet for quick access later.

If you still hit snags, share your original code and I can help debug it specifically!

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