CODEWITHSUNDEEP
excelvba
Greg B
Greg B

Reputation: 1

Using VBA to search for a text string in Excel

I'm trying to use VBA in a macro to search for a text string and delete the contents of the column. I previously found this on the website and would like to change it to search columns and delete the text "QA1" while retaining the columns. I hope this makes sense.

LastRow = Cells(Columns.Count, "D").End(xlUp).Row

For i = LastRow To 1 Step -1
   If Range("D" & i).Value = "D" Then
      Range("D" & i).EntireColumn.Delete
   End If
Next i

Upvotes: 0

Views: 153

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19847

You want to clear the contents of the whole column if one cell contains QA1?

Sub Test()

    Dim rCell As Range

    With ThisWorkbook.Worksheets("Sheet1").Columns(4)
        Set rCell = .Find("QA1", LookIn:=xlValues)
        If Not rCell Is Nothing Then
            .ClearContents
        End If
    End With

End Sub

If you want to just clear each instance of QA1 in column D:

Sub Test()

    Dim rCell As Range

    With ThisWorkbook.Worksheets("Sheet1").Columns(4)
        Set rCell = .Find("QA1", LookIn:=xlValues)
        If Not rCell Is Nothing Then
            Do
                rCell.ClearContents
                Set rCell = .FindNext(rCell)
            Loop While Not rCell Is Nothing
        End If
    End With

End Sub

Can it be written to look through the entire worksheet and delete QA1 where ever it is found?

All instances of QA1 on sheet:

Sub Test()

    Dim rCell As Range

    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set rCell = .Find("QA1", LookIn:=xlValues)
        If Not rCell Is Nothing Then
            Do
                rCell.ClearContents
                Set rCell = .FindNext(rCell)
            Loop While Not rCell Is Nothing
        End If
    End With

End Sub

Edit: Add LookAt:=xlWhole to the Find arguments so it doesn't delete cells containing QA1 and other text (e.g. QA11 or Some text QA1)

Upvotes: 1

jcarroll
jcarroll

Reputation: 577

Loops through the used range of the active worksheet, and removes the selected text.

Sub RemoveText()

    Dim c As Range
    Dim removeStr As String

    removeStr = InputBox("Please enter the text to remove")

    For Each c In ActiveSheet.UsedRange
        If c.Value = removeStr Then c.Delete
    Next c

End Sub

Upvotes: 0

gizlmo
gizlmo

Reputation: 1922

This code goes through columns in a specified row and removes the "QA1" if found

Dim LastColumn As Integer
Dim RowNumber As Integer
Dim i As Integer

LastColumn = UsedRange.SpecialCells(xlCellTypeLastCell).Column
RowNumber = 1 'Adjust to your needs

For i = 1 To LastColumn Step 1

      Cells(RowNumber, i).Value = Replace(Cells(RowNumber, i).Value, "QA1", "")
Next i

Upvotes: 0

Related Questions