VBA For loop, optimize speed and performance

**britzer** · 07-04-2014, 07:47 AM

Hi, how can I optimize this Macro? I have a lot of these and the sheets they are supposed to read from can be up to 40 columns wide and
at this time 10.000 rows. I would though like the Macro to able to read more, like 20-50k rows. I think it can but it will be quite slow.
Haven't tested it yet.

Also I don't really get if the Macro checks all rows but doesn't print. (I think it does).
If so can I make it not to, to improve performance?

Please Login or Register  to view this content.

Any help would be greatly appreciated.

/BC

**Norie** · 07-04-2014, 07:50 AM

What separator are you using?

**britzer** · 07-04-2014, 07:53 AM

Vertical Bar |, why? It cant really make any difference can it? Its used in the upload function to Database. I have a Drop down in one of the sheets where you can select which Separator you want but its vertical bar atm.

**Norie** · 07-04-2014, 08:29 AM

I was wondering if it would be possible to save as a delimited file but that's seems unlikely.

One thing you could do is use Join to concatenate the values from each row into a delimited list.

Please Login or Register  to view this content.

You could also considered putting all the data from the UsedRange in an array and work with that, it's quicker to work with data in memory than accessing it on a worksheet.

**britzer** · 07-04-2014, 08:47 AM

Ok, thanks. Can you please give any hinters on how to do that?

**Norie** · 07-04-2014, 08:55 AM

The code I posted does the first thing I suggested, did you try it?

Here's code for the second suggestion.

Please Login or Register  to view this content.

**britzer** · 07-04-2014, 08:56 AM

Oh sorry didnt look. =/ Thanks i will try and come back.

VBA For loop, optimize speed and performance

LinkBack

Thread Tools

Rate This Thread

Display

VBA For loop, optimize speed and performance

Re: VBA For loop, optimize speed and performance

Re: VBA For loop, optimize speed and performance

Re: VBA For loop, optimize speed and performance

Re: VBA For loop, optimize speed and performance

Re: VBA For loop, optimize speed and performance

Re: VBA For loop, optimize speed and performance

Thread Information

Users Browsing this Thread

Similar Threads

Is there any way to optimize this FOR loop?

how to optimize finding matching values macro speed

[SOLVED] Combine VBA Modules to Speed Performance???

Increase performance speed by removing INDIRECT functions

Optimize/Speed up macro in large data file when copying data and pasting betw. wbooks

Bookmarks

Bookmarks

Posting Permissions