CODEWITHSUNDEEP
vbaexcelrange
dave
dave

Reputation: 117

VBA Search Variable Range for text

I want to write a vba macro that searches in a variable range for the value "x" and "X" and if it finds one of These values I want to hide that row. I know how to make a range variable with range(Cells(row, column),cells(row, column)) but when I Combine the variable range with the search I can't get it runnning.

Sub zeilen_ausblenden()
    Dim count As Integer
    Dim maxrow As Integer

    maxrow = Worksheets("Anwendungen -> Prozesse ").UsedRange.Rows.count


    For Row = 11 To maxrow

        count = WorksheetFunction.Range("K" & Row & ":KB" & Row).Find("x", "X", LookIn:=xlValues)

        If count > 0 Then
        Else

            Rows(Row).EntireRow.Hidden = True

        End If

    Next

End Sub

Upvotes: 0

Views: 1259

Answers (1)

mielk
mielk

Reputation: 3940

Try this code:

Sub zeilen_ausblenden()
    Dim wks As Excel.Worksheet
    Dim found As Excel.Range
    Dim maxRow As Integer
    Dim row As Long
    '-----------------------------------------------------------


    Set wks = Worksheets("Anwendungen -> Prozesse ")
    With wks.UsedRange
        maxRow = .row + .Rows.count - 1
    End With


    For row = 11 To maxRow

        Set found = wks.Range("K" & row & ":KB" & row).Find("x", LookIn:=xlValues, MatchCase:=False)

        If Not found Is Nothing Then
            wks.Rows(row).EntireRow.Hidden = True
        End If

    Next

End Sub

Upvotes: 0

Related Questions