Workbook efficiency

[bruty]

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.

 

[Derek Brown]

Instead of using Conditional Formatting are you able to use cell validation (probably with the same formula)?
The following may also be of interest to you.
[url]http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm
[/url]

 

[bruty]

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.

 

[Derek Brown]

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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.CutCopyMode = False
End Sub

 

[Derek Brown]

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.

 

[bruty]

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.

 

[Derek Brown]

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:

if target.column = 2 then
Application.CutCopyMode = False
end if

but it is still possible that youor cells could be overwritten.