Solved: delete everything other than

[blackie42]

Hi

I have a report that is downloaded in to a spreadsheet (well 5 reports in fact). The size of each sheet is around 25,000 rows. I have to search for rejected payments and then make note of them. What I'd like to do is find each one and delete all other rows. Excerpt of sheet below

132.310 Batch Accepted361.760 Batch Accepted41.760 Batch Rejected8.690 Batch Accepted

Page 429Date 101-Sep-07

41.760 Batch Accepted383.240 Batch Rejected190.740 Batch Accepted

Not sure how to go about it really. Have used a find method before and then manipulated that row, but how to delete everything in between so I end up with just the row that has 'Batch Rejected'?

Be grateful for any help as this would speed this up no end.

thanks

jon

[blackie42]

Sorry format incorrect

should be

[361.26 0 Batch Accepted]
[254.25 0 Batch Rejected]

ie these on seperate rows

thanks

[TonyJollans]

Use an AutoFilter.

Custom Filter for Contains "Batch Rejected", or ..
Custom Filter for Does Not Contain "Batch Rejected" and delete all the rows showing.

[blackie42]

Thanks for reply Tony but have tried this way. Doesn't see the 'Batch Rejected' types for some reason - possibly because its dloaded from a mainframe application and has empty rows. Was looking for some ideas for a VB macro

5190081HX DISTN - BACS6.93E+081192.140Batch Accepted 5190082HX DISTN - BACS1.62E+08198.260Batch Accepted 5190083HX DISTN - BACS6.93E+08197.210Batch Accepted 1DL Ref 1046118 Page 3 Date 101-Sep-07 PL Data Entry Batch Takeon Report BatchDescriptionNo. Trans GrossAmount Tax Amount 5190084HX DISTN - BACS6.93E+08133.470Batch Accepted 5190085HX DISTN - BACS1.62E+08198.260Batch Accepted 5190086HX DISTN - BACS6.92E+08170.190Batch Accepted 5190087HX DISTN - BACS6.93E+081450.060Batch Accepted

Any ideas?

thanks

[blackie42]

sorry formatting completely wrong again - must get away from copy & paste from excel.

Anyway there are empty rows and headers in between a page of entries

[blackie42]

Think I might just try and find each row and copy & paste to another sheet.

[lucas]

instead of copy and paste from excel why not post a part of the data in an excel file....change any personal info so we can see what you have.

[blackie42]

thanks - heres the file

[lucas]

Turn of autofilter and run this:
Option Compare Text
Public Sub SaveRowsWithRejected()
Const TEST_COLUMN As String = "H" '<=== change to suit
Dim i As Long
Dim iLastRow As Long

With ActiveSheet
'change next line to determine which column to find last row in.
' iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If Not .Cells(i, TEST_COLUMN).Value Like "*Rejected*" Then
Rows(i).Delete
End If
Next i

End With

End Sub

[blackie42]

Thanks Lucas,

Must be doing something wrong - when I run the macro it deletes everything.

Will have another look tomorrow morning

thanks

[lucas]

you said delete all rows except those with rejected in column H....is that correct? see attached.

[TonyJollans]

Select column H and (Data > Filter > AutoFilter) twice (or in Excel 2007 go to the Data tab and click on the Filter button twice). This will give you an Autofilter of "Batch Rejected" in the dropdown in row 1 of column H.

If you really want code, this will just show the rejected ones:

Columns("H:H").AutoFilter ' Remove existing AutoFilter
Columns("H:H").AutoFilter Field:=1, Criteria1:="Batch Rejected"

This will delete everything else as well:

Columns("H:H").AutoFilter ' Remove existing AutoFilter
Columns("H:H").AutoFilter Field:=1, Criteria1:="<>Batch Rejected"
Cells.Delete

[TonyJollans]

To anybody who knows anything about these things:

Opening the attachments here in Excel 2007 seems is very slow - and I eventually get a message saying that the file attachment.php doesn't have contents according to its extension (or something like that) and would I like to open it anyway - Excel then seems to be a bit unstable. All is fine in Excel 2003.

[blackie42]

Thanks Lucas, Tony

I really want the easiest way to identify these rows and so will have a go at Tonys suggestion tomorrow at work.

I think the reason Lucas macro didn't work was because when I downloaded the whole file again I somehow changed the format in the text import wizard. As you say it does wor otherwise.

thanks again for the ideas.

I'll mark as solved once I get the solution up & running

regards

[lucas]

Tony,
I never open the attachments with the link....it causes several problems. Better to right click on it and save target as to your hard drive.

http://vbaexpress.com/forum/showthread.php?t=10626

&

http://vbaexpress.com/forum/showthread.php?t=8815

[TonyJollans]

Thanks, Steve.

I don't normally have any trouble just opening directly from the post and, having had a closer look I wonder if this is just Excel 2007 adding extra layers of security - it is hellish slow about it, whatever it does.