Workbook efficiency

bruty

Active Member
Joined
Jul 25, 2007
Messages
451
I have a spreadsheet that is used to hold a large amount of data keyed daily. The problem is I am trying to restrict duplicates (http://www.mrexcel.com/board2/viewtopic.php?p=1392318#1392318 so I am having to stick in conditional formatting. There are also formulas in that need to be copied down quite far ready for all the new data to be added.

At the moment, I have 65 columns of data and the formulas go down 10000 rows, giving me a possible 650,000 cells of data. At the moment there are 5 columns with formulas in (65,000 cells) and conditional formatting across all of them.

All this added together seems to be slowing the spreadsheet down, so I was just wondering if anyone knew of an easy way of making things more efficient?

The spreadsheet itself is only 8MB so I don't think the physical size is a problem as I have worked on much larger files in the past with no problems, so my assumptions lead to the number of formulas and the conditional formatting.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
So, with using the validation would that throw up an error when a duplicate pay number was entered?

Would this also work with copy & paste as that's the way big chunks of the data is entered.
 
Upvote 0
Yes - it will prevent duplicates being entered.
Try:
Select Cells B2 thru B20
Select Data | Validation
On the Setting TAB select Allow Custom
In the formula box enter:
=COUNTIF($B$2:$B$20,B2)<=1
Click OK
However, that does not prevent COPY/PASTE even with the workbook protected.
The solution to that is to prevent any COPY/PASTE on the worksheet.
In the Worksheet code module:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.CutCopyMode = False
End Sub
 
Upvote 0
A further thought:
The workbook could be opened with macros disabled .... so you will need to consider ensuring that macros are enabled. To do this, make your worksheets "very hidden" and unhide them in the Workbook_Open event, hiding them again in the Before_Save event etc.
 
Upvote 0
The solution to that is to prevent any COPY/PASTE on the worksheet.

Unfortunately copy & paste need to be activated as large chunks of data are input this way.
 
Upvote 0
Perhaps you need to revisit the design of the workbook to find a balance between performance and usability.
You could of course only prevent COPY/PASTE in selected columns:
Code:
if target.column = 2 then
Application.CutCopyMode = False
end if
but it is still possible that youor cells could be overwritten.
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top