Thanks (and sorry for my late reply....)
I'm afraid I reached the Max limit of excel sheet rows on that Array.
Can array split into as many sheet she needs ?
Meaning....
Is this line of code :
can be split into several sheets , let say, if the code see it reached to row 1048576 on destination range (the range of the Array to write to)?' then the final output line is [COLOR=#333333]worksheets("sheet2").range(Cells(1+blkcnt*1000,3),cells(1000+blkcnt*1000,500)=sheet2output
Yes, very easily, use a sheet count as well, so you have a three level counter, "row count" , "Block Count" and the "Sheet Count"
Set sheet count to 2 at the start,
[vba]shtcnt=2
[/vba]
then every time you increment the block count check to see if block count is over 1040 , if is it reset block count to zero and increment the shet count
[vba] If blkcnt > 1040 Then
shtcnt = shtcnt + 1
blkcnt = 0
End If
[/vba]
These additional sheets will need to be created prior to running the macro since this doens't create the additional sheets, but that can be done automatically as well if necessary
, then reference the sheet name as follows:
[vba]worksheets("sheet" & shtcnt).range(Cells(1+blkcnt*1000,3),cells(1000+blkcnt*1000,500)=sheet2outp ut
[/vba]
As a matter of interest how long is your macro taking to run now?? Have you made significant improvments?
Yes !!!
It was a HUGE improvement.
for 50,000 the code run a little more than 1 second !
I was amazed !!
Two thing:
1. I realized that the Maximum Rows that can be are : cclastrow * LastCol (the Total Rows multiply by Total number of columns, this is the Maximum if , by chance ALL criteria are match) >> I'm attaching the file so far...
2. As I need to search some results, after above code finish running , and as I have the problem of sheet rows limit,
Can the array be save to CSV file (as I read that CSV file has no limit of rows) ?
and then I will load it into Power Query module, which can deals with millions of millions rows.
In this way I can avoid the splitting of the data into several sheets.
You'll notice on attached file that I use , for checking, only 1 criteria to check 7 & 7 on 50,000 rows.
result gave me, for some reason, 50,000 instead of 200,000 (i.e: 50,000 rows X 4 columns => 200,000)
Last edited by 10shlomi10; 01-10-2017 at 03:14 PM.
If after generating all this huge amount data, you then need to process it, why don'y you process it within VBA and not write the data out to a spreadsheet at all?
This depends a bit on what processing you are doing, but if it is just a search then why not do the search directly on the huge array in VBA? This will overcome any problems of exceeding the number of rows on a spreadsheet. What processing are you planning to do on this long list?
you are absolutely right.
it will be the best thing to do. but i think it can not be done in this case and i'll explain why:
The outcome which i'm after is: Find the gap/steps between cells position, in every column.
this file is much efficient from the first one i did.
as in the beginning, i actually open every column, set the cells address every time criteria is match, in every column that is specified on the sheet that "tells" which column i want to mark/write into. and than the code fetch the the gaps between each cells, on every column.
the next step was to sort the outcome i got (per coulms and than row) and calculate the gaps.
and last step, which actually this is what i want to find , is to get the gaps that equal OR above the number I typed in text box
when I did it with a small amount of data is was great BUT
as data was grow and so as criteria , the code was running as mention for hours (3-4 hours)
I'm attaching a file which present how originally I thought to solve this.
you'll notice that on last row, on "Criteria to find" sheet, there are some cells mark in red. those cells are not mentioned on columns to mark. i did it as i need to find the gap of last row till the first mark cell above it. so I forced the code to write the cell address every time he find this Last row cell empty.
Can I find the GAPS
Your original idea of marking the cells in red which meet your requirements is only part of the processing, what are you going to do with the the cells marked in red? Or are you just using it for decoration?
An alternative to that, which is probably easier to handle when the the data set is very large is to output a list of all the cells that meet your criteria and ignore the ones that don't. This could be done entirely with VBA. You specify the "number" which you want to generate the list for at the start, then process as you are already doing, then sort the results within VBA. To do this there are a number of routines available on the web if you google "Sorting arrays in VBA". Then having sorted it you can calculate the gaps and check for which gaps are over the number first entered and only output those, then you get a nice small array to output, and never have to output your massive data array.
I am still completely in the dark as to what you are really trying to do, but at least I am pleased to have solved your orignal query as to how to speed up your routine
Best of luck.
Dear offthelip
sorry for my late reply.
Just want to say THANK YOU VERY MUCH ! for your time and efforts.
I'll consider your offer on #27 answer.
as it seems very logical to handle this problem with this kind of method.
ALL THE BEST !
Shlomi
Posting Permissions
Reply With Quote
