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

Puffy2411

New Member
Joined
Feb 20, 2019
Messages
5
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,859
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Does this do what you want. I am looking for the letter "F" in this example and clearing the contents of that row...

Code:
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
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,729
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
Code:
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

New Member
Joined
Feb 20, 2019
Messages
5
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

Well-known Member
Joined
Apr 11, 2012
Messages
11,729
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Glad we could help. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,652
Messages
5,838,597
Members
430,557
Latest member
MK15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top