Current VBA code acts up

[MrLukeSkyGuy]

Hello,

I have an existing macro I pieced together in VBA. It works most of the time, but sometimes does weird stuff. Wondering if there was a cleaner way to perform all these functions. Any help is appreciated!

Here is current code:

Please Login or Register  to view this content.

Each function broken down, including things not currently in code:
Ignore first row, this is column headers and should be left untouched
Go through column A and delete any rows > 60000000 and < 80000000
Go through column C and delete any rows that contain values. Any rows with blank cells in column C should not be deleted.
Go through column V and delete any rows that contain values. Any rows with blank cells in column V should not be deleted.
Convert all cells in column K to numbers

What do you all think?

Thanks!

[1aaaaaaaaaaaaaaa]

Hi, please wrap your code in

Please Login or Register  to view this content.

tags per forum rules-

https://www.excelforum.com/forum-rul...rum-rules.html

[MrLukeSkyGuy]

Apologies, fixed!

[1aaaaaaaaaaaaaaa]

Hiya, thanks!! Try this code out...

Please Login or Register  to view this content.

[MrLukeSkyGuy]

Hello,

It is erroring on this line:

Please Login or Register  to view this content.

Error # 1004
Method 'Union' of object '_Global' failed.

Any ideas?

Thanks!
Luke

[1aaaaaaaaaaaaaaa]

Try changing to

Please Login or Register  to view this content.

[MrLukeSkyGuy]

Same error

[Fluff13]

How about

Please Login or Register  to view this content.

[MrLukeSkyGuy]

Same error still

[Fluff13]

Do you have any merged cells?
Also is the sheet protected, or hidden?

[MrLukeSkyGuy]

No merged cells, not protected nor hidden. The data is created using access and dumped into this tab. There are several other tabs, but I changed the ws to the one being manipulated. Set ws = Sheets("Retail Report Data")

[Fluff13]

Can you supply a copy of your workbook?
It will make it a lot easier to debug.

[MrLukeSkyGuy]

Yes, here you go! Thank you for your help btw

[Fluff13]

There's nothing attached

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[MrLukeSkyGuy]

Sorry file was too big. Cut out other tabs not being used.

[Fluff13]

Thanks for that, try

Please Login or Register  to view this content.

[1aaaaaaaaaaaaaaa]

Sorry sir, that was buggy code on my part,

move the rng.delete outside of the For loop

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[MrLukeSkyGuy]

You guys rock! This code did the trick and now the macro is performing exactly as needed. WAY better than my scrapped together version. I appreciate your time, you sure will save lots of mine moving forward!

[Fluff13]

Glad we could help & thanks for the feedback

[TMS]

Late to the party but you might want to have a look at this alternative. It uses AutoFilter rather than looping. It takes about 1 second to run with your test file.

Please Login or Register  to view this content.

Can't post the code here as Sucuri dosn't seem happy about something