60,000 Rows to Delete

[Papi]

I was asked by a friend to help extract about 5,000 rows of data from an Excel 2003 workbook that could have between 35,000 and 60,000 rows of data and of the approximately 25 columns there are only two columns required. There are multiple codes in column A and the only one that they need is a code called XYZ725. I have used the macro recorder but it seems to hit that spot and then simply moves past without allowing that part of the macro to complete before it moves on to to the rest of the work required to present the data in a second workbook. Any help would be greratly appreciated.

 

[Arul.rajesh]

I was asked by a friend to help extract about 5,000 rows of data from an Excel 2003 workbook that could have between 35,000 and 60,000 rows of data and of the approximately 25 columns there are only two columns required. There are multiple codes in column A and the only one that they need is a code called XYZ725. I have used the macro recorder but it seems to hit that spot and then simply moves past without allowing that part of the macro to complete before it moves on to to the rest of the work required to present the data in a second workbook. Any help would be greratly appreciated.

Cant you sort the data, search for the code and copy to a new workbook?

 

[taurean]

Try on backup. I have considered your headers to be in Row 1 and data begins from Row 2 onwards. Change the row number to suit your needs:

Public Sub DelTheRest()
Dim r As Range, r1 As Range
Application.ScreenUpdating = False
Set r = Range("A[COLOR=red]2[/COLOR]:A" & Range("A" & Rows.Count).End(xlUp).Row). _
Find(What:="XYX725")
If r Is Nothing Then MsgBox "No such entry found!": Exit Sub
Set r1 = Range("A[COLOR=red]2[/COLOR]:A" & Range("A" & Rows.Count).End(xlUp).Row). _
ColumnDifferences(Comparison:=r)
r1.EntireRow.Delete
Application.ScreenUpdating = True
End Sub

 

[Papi]

Arul.rajesh and Taurean,

Thank you for both sets of input and advice. In fact, we used both your approaches by first copying the data into a new workbook, then ran Taurean's code as a second approach, and finally ran a modifed macro similar to the origianl macro to get the precise results required. By placing the data in the other book was the start of making it work. Taurean's code was perfect to allow the second group work with enough time and accuracy to automate the filter and then the last macro simply put the new information in the required sequence.

Many thanks for this great help. It works wonderfully.