VBA clear rows in range B3:H150 that have criteria or value of a letter

[Puffy2411]

Hello guys i have another question for you guys....
is there a VBA that can in active worksheet CLEAR the contest of a row in selected range...lets say i have text sorted in rows....and i want that i can with macro search for value or criteria text letter ž or in (english f for female) in range lets say B3:H150, so when that word is found that row is cleared in that specific range (B3:H150) so it wont effect on anything else in table...just that row when this criteria is found....and this is for multiple hits..not just one so until that row contain letter Ž is gone from this range in table...i hope you guys get what i mean......i hope you can help me

 

[igold]

Does this do what you want. I am looking for the letter "F" in this example and clearing the contents of that row...

Sub test()


    Dim rng As Range, c As Long
    
    Set rng = Range("B3:H150")
    For c = rng.Cells.Count To 1 Step -1
        If rng.Cells(c).Value = "F" Then rng.Cells(c).EntireRow.ClearContents
    Next
        
End Sub

 

[mumps]

Try:

Sub ClearRange()
    Application.ScreenUpdating = False
    Dim LastRow As Long, val As Range, sAddr As String, response As String
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    response = InputBox("Please enter the letter to search.")
    If response = "" Then Exit Sub
    Set val = Range("B3:H150").Find(response, LookIn:=xlValues, lookat:=xlPart)
    If Not val Is Nothing Then
        sAddr = val.Address
        Do
            On Error Resume Next
            Range("B" & val.Row & ":H" & val.Row).ClearContents
            Set val = Range("B3:H150").Find(What:=response, after:=val, LookIn:=xlFormulas, lookat:=xlPart, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
        Loop While val.Address <> sAddr
        sAddr = ""
    Else
        MsgBox ("Search value not found.")
    End If
    Application.ScreenUpdating = True
End Sub

 

[Puffy2411]

Thank you igold and mumps...your formulas are both good, but mumps formula does what i need when it comes to range clearing data....thank you both for your effort, and i salute you guys.

 

[mumps]

Glad we could help.