Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for the word "continued", if found, delete that row + 10 rows above
Hi There I need help with the following macro (VBA). I need to look at a file i Excel and search for the word "continued". If it is found, I need t delete that row and 10 rows above it. Can someone help me with this? Thanks Jenny : -- jriendeau ----------------------------------------------------------------------- jriendeau5's Profile: http://www.excelforum.com/member.php...fo&userid=1608 View this thread: http://www.excelforum.com/showthread.php?threadid=27534 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search for the word "continued", if found, delete that row + 10 rowsabove
I think this works ok:
Option Explicit Sub testme() Dim myWord As String Dim RowsToDelete As Long Dim FoundCell As Range myWord = "continued" RowsToDelete = 11 With ActiveSheet Do Set FoundCell = .Cells.Find(What:=myWord, _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then Exit Do End If If FoundCell.Row RowsToDelete Then FoundCell.Offset(-RowsToDelete + 1).Resize(RowsToDelete).EntireRow.Delete Else .Range("A1:A" & FoundCell.Row).EntireRow.Delete End If Loop End With End Sub Make sure you're ok with the .find parms (xlvalues, xlpart, and matchcase). If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm jriendeau5 wrote: Hi There I need help with the following macro (VBA). I need to look at a file in Excel and search for the word "continued". If it is found, I need to delete that row and 10 rows above it. Can someone help me with this? Thanks Jenny :) -- jriendeau5 ------------------------------------------------------------------------ jriendeau5's Profile: http://www.excelforum.com/member.php...o&userid=16081 View this thread: http://www.excelforum.com/showthread...hreadid=275343 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find the word "total" in a cell, delete row or column | Excel Programming | |||
Find the word "total" in a cell, delete row or column | Excel Programming | |||
Search "Total" in all worksheets and delete rows containing "Total" | Excel Programming | |||
Getting "compile error" "method or data member not found" on reinstall | Excel Programming |