HELP! Ctrl+F (or any find function) in Excel Macro

[moonlight22]

Hello,

I have a macro I required and I need part of this macro to go find in column M of my DataSheet all the occurrences of a value specified in my ControlSheet and have it remove all rows which contain this data.

The code works quite well, but the Ctrl+F does not get recorded, instead it recorded the rows that I deleted during the recording, and now each time the button which triggers this macro is pressed the same rows are deleted.

I also need to add a validation to the button that will prompt an error when the value they are looking for is not found.

A specifc note: the value in the ControlSheet (B11) is variable and changes.

Any help is greatly appreciated!!!!

This is my macro:

Sub rrrr4()'
' rrrr4 Macro
'


'
    Range("B11").Select
    Selection.Copy
    Sheets("DataSheet").Select
    Rows("10:11").Select
    Range("M10").Activate
    Application.CutCopyMode = False
    Selection.Cut
    Sheets("Records Removed").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    Columns("O:T").Select
    Selection.ClearContents
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
    Sheets("DataSheet").Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Sheets("ControlSheet").Select
    Range("B5").Select
    Selection.Copy
    Sheets("Records Removed").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(-1, 0).Range("A1:A2").Select
    ActiveCell.Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
    Sheets("ControlSheet").Select
    Range("B11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("B14").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B11").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub

 

[JackDanIce]

May not work, but try:

Sub Macro1()

Dim wsData      As Excel.Worksheet
Dim wsRemoved   As Excel.Worksheet
Dim rngSource   As Excel.Range

Dim x           As Long

Set wsData = Sheets("DataSheet")
Set wsRemoved = Sheets("Records Removed")
Set rngSource = Sheets("ControlSheet").Range("B11")

    Application.ScreenUpdating = False
    
    With wsData
        If .AutoFilterMode Then .AutoFilterMode = False
        x = .Range("M" & .Rows.Count).End(xlUp).Row
        On Error GoTo NotFound
        With .Range("M1").Resize(x)
            .AutoFilter
            .AutoFilter field:=1, Criteria1:=rngSource.Value
            .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Cut
        End With
        On Error GoTo 0
    End With
    
    With wsRemoved
        .Select
        .Range("A" & .Rows.Count).End(xlUp).Offset(1).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
    End With

    Set wsRemoved = Nothing
    GoTo NotFound

NotFound:
    Set wsData = Nothing
    Application.ScreenUpdating = True
    MsgBox "Cannot find: " & rngSource.Value & vbCrLf & vbCrLf & "Macro stopping", vbOKOnly, vbExclamation
    
End Sub