Sheet Crashing

[Turrbbo]

Hi, I'm hoping someone can help.

I've put together a spreadsheet to record vehicle details including additions and deletions and have included some macros which operate when an appropriate button is pressed to either sort, filter or copy out data to an email.

The macros all appear to work fine, the problem I have is when adding new data into the blank lines, the sheet freezes then goes into 'Not Responding' for a couple of minutes before working itself out and allowing me to move on to the next column. But, when I add data into the next cell, it does the same again.

As an example,if I add a registration number into cell 'B11' and press enter, it freezes. after a minute or two, it allows me to add a Make into cell 'C11' but, as soon as I press enter, it freezes again.

I've provided a link to the sheet; https://www.dropbox.com/s/t8egm9bjm57hder/Vehicle Schedule.V1.xlsm?dl=0

I hope someone can help as it's driving me crazy so any advice would be greatly appreciated.

 

[Fluff]

Scrub what I said in post#9
Have a look at the amount of CF you have in A11.
When I tried to delete it all, it's taken 3 mins & counting.

 

[Turrbbo]

Thanks guys, although replacing the version isn't an option and the data validation in column 'F' is a must have to ensure vehicles are allocated against the right depot (to avoid mis-spelt names, etc.) - unless anyone has any alternative ideas to get around this requirement?

 

[Turrbbo]

Hi Fluff, the code in A11 (actually between A11 and A3001) is to establish whether the line is blank or if the vehicle has an off date (and so allows it to be sorted).

I use the same code in other sheets which work fine so I didn't think this would cause the freeze.

 

[Fluff]

You've got over 100 Cf formulas in each cell in col A & from what I could see, they are all the same! That is your problem

 

[daverunt]

I have the same issue with 2013, 32 bit.
Takes a couple of minutes for Excel to be ready if I enter data in columns B,C, D etc

Out of interest I tried pasting data into the columns and that works ok?

 

[Turrbbo]

Hi, yes if I paste data it's fine, the problem is we need the cells to be completed or edited independently.

if anyone has any ideas for a workaround, I'll consider anything.

 

[Fluff]

Delete all the unneeded CF in col A, you don't need over 100 CF all doing the same thing.

 

[Turrbbo]

Fluff....you are an absolute star. I didn't realise that as the data was being added and lines re-sorted, one of the macros was copying the Conditional Format every time. Removing the CF has sorted the problem.....THANKYOU soooooo much.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Turrbbo]

Thanks Again