Hide Excel Rows Based on Another Worksheet's Criteria with VBA
Question details
Use VBA to hide rows on one worksheet when their values match criteria stored on another worksheet.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A workbook contains a client name and another field value on one worksheet and rows to evaluate on a different worksheet.
- Observed behavior
- Rows matching both criteria should be hidden, while other evaluated rows should remain visible.
Confirm the worksheet names, criteria cells, data columns, and header row before adapting the macro. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) and allow macros only if you trust the file.
Use VBA to compare criteria and hide matching rows
Run a macro that reads two criteria from Sheet1 and applies them to rows on Sheet2.
This example reads the client name from Sheet1 cell B1 and another field value from B2. It compares them with columns A and B on Sheet2, starting at row 2, and ignores differences in capitalization.
The macro unhides evaluated rows that do not match both criteria. It uses the last populated cell in column A to determine where to stop.
In Excel, select Developer > Visual Basic. If the Developer tab is not visible, enable it in Excel's ribbon customization settings.
In the VBA editor, select the workbook, then choose Insert > Module.
Paste this code into the module: Sub HideMatchingRows() Dim criteriaSheet As Worksheet Dim dataSheet As Worksheet Dim clientName As String Dim fieldValue As String Dim lastRow As Long Dim rowNum As Long Dim matches As Boolean Set criteriaSheet = ThisWorkbook.Worksheets("Sheet1") Set dataSheet = ThisWorkbook.Worksheets("Sheet2") clientName = Trim(CStr(criteriaSheet.Range("B1").Value)) fieldValue = Trim(CStr(criteriaSheet.Range("B2").Value)) If clientName = "" Or fieldValue = "" Then MsgBox "Enter both criteria in Sheet1 cells B1 and B2." Exit Sub End If lastRow = dataSheet.Cells(dataSheet.Rows.Count, "A").End(xlUp).Row For rowNum = 2 To lastRow matches = (StrComp(Trim(CStr(dataSheet.Cells(rowNum, "A").Value)), clientName, vbTextCompare) = 0) And _ (StrComp(Trim(CStr(dataSheet.Cells(rowNum, "B").Value)), fieldValue, vbTextCompare) = 0) dataSheet.Rows(rowNum).EntireRow.Hidden = matches Next rowNum End Sub
Change Sheet1, Sheet2, B1, B2, and the data column letters as needed. Return to Excel, select Developer > Macros, choose HideMatchingRows, and select Run.

Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for common Excel formats, making it easy to move everyday spreadsheet work. It does not fix Excel VBA issues; macro support depends on the WPS edition and platform.

Frequently Asked Questions
How do I show a row again with VBA?
Set its Hidden property to False, for example: Worksheets("Sheet2").Rows(10).EntireRow.Hidden = False.
Can I hide rows that match only the client name?
Yes. In the macro's matches assignment, remove the second comparison and the And operator so it tests only column A against clientName.
Why does the macro stop before some of my data rows?
The example finds the last row using column A. If that column is blank on later data rows, change both references to "A" in the lastRow statement to a column that is populated through the end of the data.
How do I keep the macro when I save the workbook?
Choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file cannot retain VBA macros.




