Find Text & Delete All Rows Below

seedub · May 16th 2008

I have a report that comes in a txt file. After importing into excel i am left with a bunch of garbage that i dont need. This report is anywhere from 5-15 pages depending on how much product was made that shift. I only need the information off of the 1st page.

My question is how can i Find the first occurance of specific text (Site) (will be in column A) and have it select that row and all rows below and delete them.

My biggest issue is the first page can be anywhere from 40-60 rows so I need to find the text (Site) and delete everything below it, which could be anywhere from 500-1500 rows.

Any suggestions?

Robert B · May 16th 2008

Re: Find And Delete All Rows Below

Hi, and welcome to the forum

try

Code

Dim LastRow As Long, myCell As Range, myRange As Range
Dim myCell1 As Range


LastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
Set myCell1 = Range("A" & LastRow)
    Cells.Find(What:="Site", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
        Set myCell = ActiveCell
        
        Set myRange = Range(myCell, myCell1)
        myRange.EntireRow.Delete

Display More

Hope that helps

Robert

seedub · May 16th 2008

Re: Find And Delete All Rows Below

Worked like a charm!!
Thanks alot

Muchly Appreiciated

Find Text & Delete All Rows Below

Participate now!

Share