SEARCH AND DELETE MACRO

  • Thread starter Thread starter JAJOSEPHESQ
  • Start date Start date
J

JAJOSEPHESQ

I am looking for a macro to look up the active cell in one workbook in
a spread sheet, then go to another workbook with in the same spread
sheet and look for the contents of the active cell within a named
range.

I don't know if I could then have the maco delete the corresponding
line which containted the match to the find funciton of the active
cell.
 
Maybe something like:

Option Explicit
Sub testme()

Dim myLookFor As Range
Dim myLookInRng As Range
Dim FoundCell As Range


Set myLookFor = ActiveCell 'on the activesheet
Set myLookInRng = Worksheets("sheet2").Range("myRangeNameHere")

With myLookInRng
Set FoundCell = .Find(what:=myLookFor.Value, _
after:=.Cells(.Areas(.Areas.Count).Cells.Count), _
LookIn:=xlValues, lookat:=xlWhole)

If FoundCell Is Nothing Then
'do nothing
MsgBox myLookFor.Value & " wasn't found"
Else
FoundCell.EntireRow.Delete
End If
End With

End Sub
 
I think you mean to say that you have a value in the active cell of the
active sheet and you want to search for this value in a named range on
another sheet. Then delete the entire row of the original active cell. Is
that right?
Say the named range is "NamedRange". The following macro will search
and produce the cell address of the found cell in the named range. It will
also produce a message box saying the value could not be found if it, in
fact, could not be found. If it is found, the macro will delete the entire
row of the original active cell. HTH Otto
Sub FindIt()
Dim FoundCell As Range
On Error Resume Next
Set FoundCell = Range("NamedRange").Find(What:=ActiveCell.Value,
LookAt:=xlWhole)
If FoundCell Is Nothing Then
MsgBox "Not found."
On Error GoTo 0
Exit Sub
End If
On Error GoTo 0
MsgBox FoundCell.Address
ActiveCell.EntireRow.Delete
 
jajoseph

Just to help clarify terminology.

A "spreadsheet" is the generic term for any number-crunching application's
file.

In Excel, each *.xls file is a "Workbook". Each "Workbook" contains one or
more "Worksheets".

I believe you are referring to one Workbook with at least two Worksheets.

See Dave's and Otto's posts for suggestions.

Gord Dibben Excel MVP
 
Sorry for the wrong use of terminology!!!


Gord Dibben said:
jajoseph

Just to help clarify terminology.

A "spreadsheet" is the generic term for any number-crunching application's
file.

In Excel, each *.xls file is a "Workbook". Each "Workbook" contains one or
more "Worksheets".

I believe you are referring to one Workbook with at least two Worksheets.

See Dave's and Otto's posts for suggestions.

Gord Dibben Excel MVP
 
Back
Top