PDA

View Full Version : Solved: Another delete rows query



blackie42
12-05-2007, 06:21 AM
I have a spreadsheet that is imported from a mainframe application.
Problem is it has a lot of headers and other stuff that isn?t needed

What I need to do is

1) Find the word ?POLICY? in col A and delete all rows above it
2) Find the word ?POLICY? in col A again but this time only delete
rows above it up to the last 10 number account number.
3) Repeat until no more ?POLICY? words

e.g.

Headers, other stuff ?--- delete
POLICY
1234567890

0987654321

Headers , other stuff ?--- delete

POLICY
6549871230

5649871231

2321564256

Header, other stuff ?--- delete
POLICY

any help v much appreciated

regards

Jon

Bob Phillips
12-05-2007, 07:05 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim cell As Range

With ActiveSheet

Set cell = .Columns(1).Find("POLICY")
If Not cell Is Nothing Then

.Rows(1).Resize(cell.Row - 1).Delete

Set cell = Nothing
Set cell = .Columns(1).Find("POLICY", .Range("A1"))
If Not cell Is Nothing Then

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = cell.Row To 2 Step -1

If IsNumeric(.Cells(i, "A").Value) Then

If Len(.Cells(i, "A").Value) = 10 Then

.Rows(i + 1).Resize(cell.Row - i - 1).Delete
Exit For
End If
End If
Next i
End If
End If
End With

End Sub

blackie42
12-05-2007, 08:06 AM
Thanks very much XLD - I have tested it with a mock up and it works great (as I expected it would). Will download the mainframe file and do some further testing - may need a couple of tweaks.

thanks again

regards

Jon

blackie42
12-06-2007, 03:26 AM
Another query related to this...

Just a little problem as sometimes there isn't anything in col A but there is in other cols.

I can get it sorted so that the account numbers are all together but there are other lines in between e.g.

colA

blackie42
12-06-2007, 03:32 AM
Oops pressed the enter by mistake....

colA ColB ColC

1111111111
1111111111
rubbish
rubbish
2222222222
2222222222

3333333333
3333333333
rubbish

Is there any way (working with column A) that I can get 1 empty row between the accounts. Sometimes there a number of 'rubbish' rows between them others there aren't any like

POLICYNAR DD AMOUNTEQ/TH AMOUNTッッッッッッッッッ ッッッッッッッッッッッッッッッッッッッッッ6052002P9505007483356100748335610074833561007483356100 7483356107792454P32002000877586402087758640208775864020877586402HL.2020THRE ADS BACS PAYMッッッッッッッッッッッッッッ ッッッッ ッッッッSCHM FND/CLS Uッッッッ ッッッッッッッ ッッッッッッッッッッッッッッッ ッッッッ ッッッッPOLICYNAR DD AMOUNTEQ/TH AMOUNTッッッッッッッッッ ッッッッッッッッッッッッッッッッッッッッッ6097598P0583.33583.33008225311000822531100082253110008 22531107624986P910010008622651050862265105086226510508622651057629192PA2502 500862616200HL.2020THREADS BACS PAYMッッッッッッッッッッッッッッ ッッッッ ッッッッ

blackie42
12-06-2007, 03:33 AM
Sorry - forget this - the formatting is all wrong.