0

I am quite new to macros and VBA etc.

I have a worksheet which is used as a calculator to cost up jobs. Some cells are input cells, where the Estimators can type in dimensions/choose material types etc, and other cells are locked because they contain formulas which work out the costings.

I am now adding a 'Reset All' button to the calculator, to clear all the input cells so that the Estimators can easily start from a blank sheet, rather than manually going through and deleting out their entries.

I have inserted a Command button and linked this to a code which looks something like this (but much longer):

Sub ResetAll()
'
' ResetAll Macro
'

'
    Range("A8:C17").Select
    Selection.ClearContents
    Range("AT382:AX382").Select
    Selection.ClearContents
    MsgBox "Calculator Reset"
    ActiveWorkbook.Save
End Sub

I am regularly making improvements to the calculator, and adding lines in. My issue is that when I add lines in, my code still refers to the same cells. So if I add a line above AT382, the cell I want to clear now has the reference AT383, but the code is for clearing AT382.

I have done some research but have been unable to find any solution to this as yet.

I would appreciate your help - many thanks!

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
louwg
  • 23
  • 4
  • Have you considered a sheet for data and a separate sheet for calculations with that data - means clearing cells can be easier... – Solar Mike May 14 '19 at 11:08
  • name the cells. Names move with the cell when rows and columns are added. Then in your code refer to them by name as Range("myname") etc. Incidentally don't use select in your code. You can just use Range("myname").ClearContents - no need to select – Harassed Dad May 14 '19 at 12:35
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ May 14 '19 at 12:42

0 Answers0