Macro needed to clear contents

[bigband1]

Hello All
On the attached worksheet i have 2 columns of data sorted sequentally in cols A&C,in col AN i have related data.I wish to use only the last 5 or less sets of data for each name from the most recent date,if possible using a macro to clear the unwanted data.the worksheet is much longer than that shown.On sheet 1 have highlighted the data to be removed and left empty rows between the data for illustration only.Sheet 2 shows the desired result.Many thanks for help given.

[Greg M]

Hi there,

Take a look at the attached workbook and see if it does what you need.

It uses the following routine:

Please Login or Register  to view this content.

This statement allows you to specify how many records will be contained in each group of entries after the deletion process has been performed.

This statement allows the routine to work with the current layout of your worksheet - i.e. with one blank line separating each group of entries. If your final layout does not include these separating lines just change the number of blank lines to zero in that statement.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[bigband1]

Thanks for the reply Greg
When i put the macro in my personal macro file & run it in sheet1 of another workbook getting error message
Run-time error 5
When debug used it shows
Set rFirstCell = Intersect(.Rows(iFIRST_ROW), _
rColumnToTest)
I would like to use it in a number of workbooks.
Thanks for your help

[Greg M]

Hi again,

Ok, that shouldn't be a problem - just change the highlighted line of code:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

To avoid accidentally using this routine on a workbook for which it is not intended, you should probably change the name of the worksheet from the ever-popular "Sheet1" to something a bit more distinctive (e.g. Music Database), and amend the following line to incorporate the new worksheet name:

Please Login or Register  to view this content.

Hope this helps - as before, please let me know how you get on.

Best regards,

Greg M

[bigband1]

Hi Greg,
Thanks for the reply.Used activesheet as suggested,it worked fine with 23000 rows,but when tried with 40000 & 80000 rows,getting runtime error 6 "overflow debug gives "For i = rColumnToTest.Cells.Count To 1 Step -1".
Perhaps because im using the full data in all rows & columns from A to BO?
Thanks again

[JOHN H. DAVIS]

Another:

Please Login or Register  to view this content.

[Greg M]

Hi again,

I don't often work with very large worksheets, so that's probably why I tend to use Integer data types instead of Long ones - I know, I know, bad practice, but old habits die hard!

I think it will work for you if you change the following line:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

Integer data types can have a maximum value of 32,767 - the routine crashed when the number of data rows on your worksheet exceeded that value.

Sorry about that mistake - please let me know if the above correction works for you.

Best regards,

Greg M

[bigband1]

Hi Again
Thanks for your reply Greg,Changed to As Long,works fine on 32700 rows,but getting error message when tried with 35000 rows,also its not removing the data in date sequence.I wished to leave the last 5 names by date sequence most recent date coming last.John H.Davis solution deletes everything but the last five rows.
Would a formula work better perhaps,with a helper column.
Thanks to both for your help.

[JOHN H. DAVIS]

It worked on the sample file in Post 1 to produce the results as shown on Sheet2? The dates in that case were sorted. Is that not what you wanted? "2 columns of data sorted sequentally in cols A&C,in col AN i have related data.I wish to use only the last 5 or less sets of data for each name from the most recent date,"

[Greg M]

Hi again,

I've run the routine on a workbook containing 93,000+ rows, and no error occurred.

Regarding the "non-latest" results being deleted, try commenting out the highlighted line below:

Please Login or Register  to view this content.

This will show all of the rows marked for deletion - they are always the "uppermost" entries in the group. If the records have not been sorted in date order, then incorrect records will be deleted.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[bigband1]

Apology to John H Davis your solution does work as does Greg's.Many thanks to both for your help.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.

[Greg M]

Hi again,

Many thanks for the feedback and also for the Reputation increase

Very pleased that I was able to help.

Best regards,

Greg M