Excel VBA how can we clear excel memory without saving the workbook

[kashif.special2005]

Hi,

I have a workbook with multiple sheets with the data around 4 lacs in each sheet, and from vba I am manipulating sheets data, like filling the value in cells or deleting the rows etc and also applying index match formula from VBA.


sheet1:- 40000 rows data with column customer_id
sheet2:- 400000 rows data with multiple columns including column customer_id

In a sheet1 I am fetching some column information from sheet2 with index match function, 1st time it takes some time, I can understand that because of huge data in sheet2, after applying formula I am doing some data deletion in sheet2 from vba, after deleting data the sheet2 have only 30000 rows data.

Now again I am applying index match formula in sheet1, still it is taking that much of time, and I notice that when I go to sheet2 and scroll the row, the row selection bar from right ends at the row number 400000, which is the old row number, now the trick is when I save the workbook, it refreshes the excel memory, and after refresh the excel memory when I go to sheet2 again and scroll the row, this time rows scrolls on actual visible data that is 30000, and the formula index match function fetches the value very fast, because after refreshing the excel memory it takes less data in the memory.

The problem is that I don't want to save the workbook in middle, because the workbook is very heavy and it takes lot of time to save.

my question is how can I refresh excel memory from vba without saving the excel workbook?

I hope I put my query in detail.

Thanks
Kashif

 

[Ruca13]

Hi,

I think of 3 different possibilities:

1:

[COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] x[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR]<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">[COLOR=#303336][FONT=inherit] 
ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]UsedRange
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR]</code>

May do the trick but never tried it

2:
Define the last row as to where the range on the formula should end, something like

lastrow = sheets(2).cells(1,1).end(xlDown).row

Then use it in the range of the index match for the last row of the range, this way the formula will only run till there. It won't refresh the excel, but the formula should run quicker

3:
Copy the information of sheet 2 to a new sheet, and delete the old sheet 2. This way the end of the sheet will be refreshed. The problem will be the formulas can no longer refer to the old sheet (REF error). I personally use this to run on a daily report, but since I only do it once, I do not have that problem. It was not clear in your case.

Hope it helps.

 

[kashif.special2005]

Hi Ruca13,

Thanks for reply, I also like your idea in point 3, and incorporated in my code, and it is working like a fly.

Thank you so much for your precious time.

Thanks
Kashif