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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.