Speeding up vba macro multiple for loops

[kpayney1]

Hi guys i have a code that loops through the columns, makes new sheets for each one and then does some formatting depending on the acronoym in the matrix underneath. Anyway to speed it up? On a laptop its taking a few minutes to run.

Please Login or Register  to view this content.

[nankw83]

Hi kpayney1 & welcome to the forum

Try changing the below part of your code ...

Please Login or Register  to view this content.

with this

Please Login or Register  to view this content.

[kpayney1]

Hi Nankw83, Thanks for the quick response. That code is bringing up a 1004 error?

[nankw83]

It is compiling with no errors at my end. Can you attach a sample file so we could test & identify the issue

[kpayney1]

File Attached

[kpayney1]

Hi Nankw83, attached file above. Appears to be hitting an error on the autofilter line. Can you explain the .offset= evaluate line please?

[AB33]

nankw83 Code is faster and leaner, but it errors as .Offset(, 3) = Evaluate("if(isnumber(--(left(" & .Address & ",1))),""x"","""")") returns null, hence for error on filter.
I have removed two loops and merged them.
The code will error on newSh.Name = "GCID" if sheet GCID already existed.

Please Login or Register  to view this content.

[nankw83]

I made few changes to the code ... Also, I moved it to a module & rename macro name. Please check attached file which seems to work

Please Login or Register  to view this content.

[kpayney1]

[QUOTE=AB33;5658235]nankw83 Code is faster and leaner, but it errors as .Offset(, 3) = Evaluate("if(isnumber(--(left(" & .Address & ",1))),""x"","""")") returns null, hence for error on filter.

Awesome I'll give yours a try. Can you please explain the .offset code line? Assuming .offset is the object column 3 and the if statement looks for numbers but not understanding the excessive "" and & comments

[kpayney1]

Hi Nankw83 can you explain the offset = evaluate line? Looks like a good piece of code to keep in the memory bank.

[mjr veverka]

The "Trial" file is inadequate to the presented code.

[AB33]

.Offset(, 2) = Evaluate("if(isnumber(--(left(" & .Address & ",1))),""x"","""")")
Means 2 columns to the right of column A, which is column C.
Evaluate extracts left function for any cell that has a number.

[sintek]

GCID = RemoveInvalidCharacters(newSh.Range("B2"))

Not sure what characters you are wanting to replace as B2 has no values...
Also, what shall new tabs be named...
Will amend once I understand the above...

This, however, will be much faster...

Please Login or Register  to view this content.

[kpayney1]

Hi Sintek, Originally on each new sheet the B2 was pulled from the job role name row in the entry sheet and that row had special characters in it so i was using regex to strip the characters then check if it was under 32 char so that it could be used for the sheet name. Your code works perfectly, I think the B2 issue is from my end as the file ive got has an extra row as below. Once i replace the entry sheet with the full version and comment out the merge line it works perfectly. Would you be able to comment your code so i may learn the differences?

[kpayney1]

Also sintek attached is the correct sheet with row layout. The GCID code is used to strip D4 to AJ4 of special characters then uses those cells as names for each sheet in the loops.

[sintek]

I only see 2 special characters...And ideally a space should remain between sheet name strings...
This should cover all...I will comment code if this all works as it should...

[kpayney1]

I only see 2 special characters...And ideally a space should remain between sheet name strings...
This should cover all...I will comment code if this all works as it should...

Originally the role names had a few more / and - but where stripped down since I made the code. It all works fine on my end! Thanks alot I'd love to see the comments to learn how you made it so fast.

[sintek]

Post 16 file has been updated with comments...Securi Firewall not allowing code posting...

Thanks for rep +

[kpayney1]

Post 16 file has been updated with comments...Securi Firewall not allowing code posting...

Thanks for rep +

Thanks Sintek! and no problems. Between you and nankw83 I have picked up a few new ways of speeding my code up.