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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,506
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
9,589
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,437
Messages
5,528,761
Members
409,834
Latest member
vexceled

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top