Excel Database slows down as number of entries rises

[chun25011]

Hi Everyone,

I am now building an Excel Database and I use Excel VBA to help (Entry form....etc.)

I have now approximately 200 entries in my database. And I can notice that it starts to slow down, as I enter an new entry, or when I scroll through the database.

Would you have any idea how to resolve this issue? Would it be caused by the VBA code? ( in that case I should try simplifying the code), or is it simple the Excel sheet storing too much data?

Thanks in advance.

[Roel Jongman]

200 records is not that much.. when you consider that excel has one miljon rows..
So it is very likely it is the vba code. But what it is is very hard to guess without seeing the code..

[TMS]

When you say "Excel Database", are you referring to a two dimensional data table on a single worksheet?

If so, how many records do you anticipate your database holding?

200 is not many records and shouldn't be slow to work with.



Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

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

[chun25011]

Thank you so much for your reply guys.

So here are the Before and After attached database files.
The data is partly in French. Basically, there are two sheets: "Saisie" for data entry, and "Base" for the database

Entering the data in the sheet "Saisie" and then the data stores in the sheet "Base".

There is a window of "Last Entries" in the sheet "Saisie" in order to monitor the entry progress without going into the "Base" sheet all the time.

Currently, there are only around 140 entries in the sheet, and you could tell that without all the data in "Base" in the Before file, it's much quicker to scroll through than in the After file.

This may be due to the fact that I store a lot of texts in a column F in "Base", texts that we can display by clicking on the cells.

The VBA module "AddData2" (associated to the button "Add Data" in "Saisie" is also slowing down over time, as the number of data rises. But I guess it is quite long and some parts may be able to be simplified.

Thank you SOOOOO much guys for your help, any suggestions will be largely appreciated.

Thanks a lot!

[chun25011]

And ultimately, we were estimating a total number of entries of 70 000...

And I found a solution to avoid the table from slowing down:

1. the code on the "Base" sheet, with the camera function and the Picture really seems to slow down the whole thing
2. I hided the contents of the cells that are "shrink fit" (column F), and it gets back to normal again!

Thanks a lot!

[TMS]

Glad you got it sorted

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.