Macro to Find and ReplaceClosed

Question

Hello, 

I am trying to create a macro that can Find and Replace a variable value. For example, I want to create a cell that can have any code entered and the macro will find and replace this value in the given range.

I have so far tried to just record this but it does not pick up on the find and replace function. Regardless of what I require the macro to find, I would like the replacement to be blank (""). This enables me to use the Go To > Special and delete entire row of the blank cell.

This function is required to delete old data from my file which will be decided manually via entering the value to be deleted into the specified cell. 

So far I have ...

Sub Delete_Specified_Workitems()

Dim Range1 As Range

    Set Range1 = Range("D32")
    Sheets("Data Dump").Select
    Cells.Replace What:=Range1, Replacement:=(""), LookAt:=Range("A:A"),  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Sheets("Controls").Select
    
End Sub

This is resulting in an error 'Type: Mismatch'.

Any help will be greatly apprciated!

TrowaD · Answer

Hi Sarah,

Please make a backup of your file first, since deleted data can't be retreived.

The following code will search column A for the value entered in D32. When a match is found, it's row will be deleted.
Sub DeleteRow()
Dim lRow As Integer
Dim sValue As String
lRow = Range("A" & Rows.Count).End(xlUp).Row
sValue = Range("D32").Value
For Each cell In Range("A1:A" & lRow)
If cell.Value = sValue Then cell.EntireRow.Delete
Next cell
End Sub
Best regards,
Trowa

Macro to Find and Replace

1 response

Similar discussions

Subscribe To Our Newsletter!