Size Matters - minimizing file size

[jamie.cutting]

I have a worbook containing several macros and lots of formulas that
has grown to big. The only way to trim the file size down is to delete
rows containing formulas that are as of yet unused but will be as more
data is entered. These cells will eventually be required to carry out
calculations on cells populated by the user. I.e columns A-C are text
id's, D:AG is data entered by the user and AH:CT is formulas that
manipulate the data with a corresponding outcome in column CU that is
acted on by a macro.

Is the best thing to have a workbook macro that copies the formula in
columns AH:CT when the user inputs text in column B? Does anyone know
a simple way to implement this?

Any help gratefully appreciated

Regards
Jamie

 

[Steve Hunter]

Hi Jamie,

Something like this should work:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Columns(2), Target.EntireColumn) Is Nothing Then
Range("AH2:CT2").Copy Intersect(Target.EntireRow, Range("AH:CT"))
End If
End Sub

This assumes that the formulas can be found at AH2:CT2. If you need a larger
range to trigger the formula copy, replace Columns(2) with e.g. Range("A:C").

Hope this helps

Steve