Previous Topic Next Topic

Macro to Find and Replace

[Closed]
Report
-
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 -
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!

1 reply

Answer 1 / 1
Posts
2809
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 23, 2021
 484
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
Ask a question