Speed up a macro...

[kokapelly]

Hi all,

So below is the code i use to convert column B from yyyymmdd to mm/dd/yyyy:

Please Login or Register  to view this content.

I have to do this for 6000 lines and this is taking way to long. I have "Application.ScreenUpdating = False" at the start is there anything else i can do to speed it up?

Thank you

[Norie]

Select the column, goto Data>Text to columns..., on the 3rd step select YMD from the column data fomat dropdown and click Finish.

[kokapelly]

You mean doing this manually?

[kokapelly]

I have no problem doing this my self.... the problem is this is a big macro and process that i am streamlining. the code above is just one very small part of the macro. but it is the one that takes time.

[duncandhu]

How long is it taking you currently?

It doesn't seem like it should take a long time.

[larai]

You can always record the process that Norie posted and incorporate that into your macro. And I've found looping through ranges using the collection is slower than using a numeric variable to step through row numbers.

[kokapelly]

10 to 15 min.

So my other question is there a way i could filter column B with the date as it is yyyymmdd vs. changing the column then using:

Please Login or Register  to view this content.

[kokapelly]

Larai.... you talking way over my head.... Not a very good writer.

[larai]

Why not perform your date calculation, change the calculated date to yyyymmdd and set your filter accordingly?
strFilter = format(date()-180,"yyyymmdd")
Criteria1:="<=" & strFilter

You might have to tweak a bit, I haven't tested it.

[kokapelly]

i'll give that i try... again...

[kokapelly]

Seams like that worked fine.. will need to run this live a few times... but Thank you Larai!!

[tigeravatar]

Here is the VBA version for text to columns to convert a number stored as yyyymmdd to a date of mm/dd/yyyy:

Please Login or Register  to view this content.

The code completed basically instantly for me on a test set of 10,000 rows.