Previous Topic Next Topic

Macro to Find and Replace

Closed
SarahC - Mar 13, 2012 at 06:39 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Mar 19, 2012 at 11:26 AM
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!
Related:

1 reply

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 19, 2012 at 11:26 AM
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
0