[b]Excel Macro Help[/b] 1

[Texanite]

I have tried and tried to set up a macro to find and delete the next 8 lines within the spreadsheet. Nothing seems to work! Below is the Macro. Can anybody fix this or tell me what I am doing wrong? The Macro quits after the first search. It acts like it is operating as an absolute but I tried changing it to relative by pressing the relative/absolute button and get the same thing. I am running an old version of Windows NT service pack 6. Below is the macro:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 9/7/2004 by Marcia Black
'
' Keyboard Shortcut: Ctrl+Shift+Z
'
Cells.Find(What:="Examination Mgmt Services Inc", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
ActiveCell.Rows("1:8").EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
Cells.FindNext(After:=ActiveCell).Activate
ActiveCell.Rows("1:8").EntireRow.Select
End Sub

 

[SkipVought]

Marcia,

Try this

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 9/7/2004 by Marcia Black
'
' Keyboard Shortcut: Ctrl+Shift+Z
   With ActiveSheet
       Set r = .Cells.Find(What:="Examination Mgmt Services Inc", after:=.Cells(.Cells.Rows.Count - 1, 1), searchdirection:=xlPrevious)
       .Range(.Cells(r.Row, 1), .Cells(r.Row + 7, 1)).EntireRow.Delete shift:=xlUp
   End With
End Sub

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[Texanite]

I copied it over my macro and it just sits there and bleeps at me everytime I hit he shortcut... Nothing happens.

 

[SkipVought]

Hmmmmmm???

It works for me.

Did you paste the code in a MODULE and not a Worksheet Object in VBA?

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[SkipVought]

Also...

make sure that the keyboard shortcut is still associated with the macro.

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[Texanite]

I went to Macro, Edit and pasted your macro then saved it. Don't know whether this has anything to do with it or not but this spreadsheet was imported from Oracle. All text is in column A. But that shouldn't make any difference should it? When I recorded the macro, column A was the only thing I referenced.

 

[Texanite]

Keyboard shortcut is still associated with the macro.

 

[SkipVought]

The macro deletes the rows regardless what column the string is in.

Put a break on the first executable statement and then run the macro. It should stop at the break.

Are you sure that the search string exists in the sheet?

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[Texanite]

Skip, you're getting way over my head. Would you be amenable to my sending you the file and taking a look at it to see if you can figure out what's wrong with the macro?

Thanks,
Texanite

 

[SkipVought]

skip.metzger at lmco.com

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]