filter/edit time and date

[Gerryduda]

Hello,

9/18/10 21:32:44 165.138 51.593 0.0004 0.0001 19.8029 2

I have an spreadsheet with 68,000+ entries in it. As above

This data was to be recorded every 20 sec. for 24 hours but was mistakenly set to every 2 seconds. The data is then entered into another program.

The time and date are in the A1 row columb of excel 2003.

I need to delete 9 rows, then keep a row and delete the next 9, etc. etc.

With 68,000 plus lines manually deleting could take days.

Any ideas

[jeffreybrown]

Hello and Welcome to the Board,

Number the first 10 rows 1 through 10. In my test I did this in column A

After the macro has run you should now have 1 through 10 6800 times. Turn on your filter and on the new column with numbers select all but the number 10, go to Ctrl + G >> check visible cells only and then delete.

You should now only have row 10's left.

Please Login or Register  to view this content.

[Gerryduda]

Hello and Welcome to the Board,

Number the first 10 rows 1 through 10. In my test I did this in column A

After the macro has run you should now have 1 through 10 6800 times. Turn on your filter and on the new column with numbers select all but the number 10, go to Ctrl + G >> check visible cells only and then delete.

You should now only have row 10's left.

Please Login or Register  to view this content.

this almost worked. First I tried with the whole page and got a prompt about the paste area
does not match the cut area or something. So I pasted 50 lines into a new spreadshheet and got another error.


the following was was highlighted yellow in the editer when I hit debug.

Rng.Copy Range("A" & Lr)


I did see the row numbers change before the error.

[Gerryduda]

Thanks for the help!

I fiddled around a bit and it worked for renumbering the rows.

You saved me a lot of time.

Gerry

[martindwilson]

you could do that with advanced filter
say you had header in row 1 and wanted rows 2,12,22,...........
you could put in a spare column say H leave h1 blank in h2 =MOD(ROW(A2),10)=2
then select a column of your data say A use advanced filter with h1:h2 as criteria range

[jeffreybrown]

Hi Martin,

I just tried your suggested way...very slick. Thanks for sharing.

Jeff