I have been trying to write a macro to exclude rows containing \ in column V. The data I will be working with will have a variable number of rows so I cant specify the last row. I have found various codes but none of them seem to work. The current one I am working on is a mix of two. I have no experience of working with VBA so the simpler the better.

Sub HideRowsContainingContent()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchText As String
    Dim lastRow As Long
    
    ' Set the worksheet you want to work on
    Set ws = ThisWorkbook.Sheets("R filtered")
    
    ' Define the content to search for
    searchText = "\\"
    
    ' Find the last row with data in the worksheet
    lastRow = ws.Cells(ws.Rows.Count, "V").End(xlUp).Row
    
    ' Loop through each cell in the used range
    For Each cell In ws.Value = "\\" Then Each cell.EntireRow. Hidden = True
    Else
    For Each cell In ws.EntireRow. Hidden = False
    End If

Each of the For Each statements under the Loop through section, generated the compile error Expected: end of statement.

Thanks Bex

I have been trying to write a macro to exclude rows containing \ in column V. The data I will be working with will have a variable number of rows so I cant specify the last row. I have found various codes but none of them seem to work. The current one I am working on is a mix of two. I have no experience of working with VBA so the simpler the better.

Sub HideRowsContainingContent()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchText As String
    Dim lastRow As Long
    
    ' Set the worksheet you want to work on
    Set ws = ThisWorkbook.Sheets("R filtered")
    
    ' Define the content to search for
    searchText = "\\"
    
    ' Find the last row with data in the worksheet
    lastRow = ws.Cells(ws.Rows.Count, "V").End(xlUp).Row
    
    ' Loop through each cell in the used range
    For Each cell In ws.Value = "\\" Then Each cell.EntireRow. Hidden = True
    Else
    For Each cell In ws.EntireRow. Hidden = False
    End If

Each of the For Each statements under the Loop through section, generated the compile error Expected: end of statement.

Thanks Bex

• row
• hide

1 Answer 1

The basic of your code is working, only the exact syntax should be applied

• Quotes mean string type no need to escape
• For Each loop needs Next at the end.
• InStr function looks for a character in a string, and returns the position.

Sub HideRowsContainingContent()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchText As String
    Dim lastRow As Long
    
    ' Set the worksheet you want to work on
    Set ws = ThisWorkbook.Sheets("R filtered")
    ' Define the content to search for
    searchText = "\"    
    
    ' Find the last row with data in the worksheet
    lastRow = ws.Cells(ws.Rows.count, "V").End(xlUp).Row
    
    ' Loop through each cell in the used range
    'For Each cell In ws.Value = "\\" Then Each cell.EntireRow. Hidden = True
    'Else
    'For Each cell In ws.EntireRow. Hidden = False
    'End If
    For Each cell In ws.Range("V1:V" & lastRow)
        If InStr(cell, searchText) > 0 Then
            cell.EntireRow.Hidden = True
        Else
            cell.EntireRow.Hidden = False
        End If
    Next cell
End Sub