Forum Discussion
Excel not updating with Macro
Hello New to Macros. Managed to record a macro. When I run the macro and then delete rows, certain cells don't update their values. For instance C8 should equal C41. I deleted row C39-60 but obvio...
That is a general problem with using direct cell references from VBA. If changes are made to the worksheet it is normal for the code to break. The way to make the code more robust is to use defined names or Excel Tables (I restrict myself to defined names or table references on the worksheet too, but that is not general practice).
If you define a named range you will be able to go to it to see what it contains and if you delete rows the range adjusts with the content. Within the code you have statements such as
Sub test()
Dim Rng As Range
Dim v As Variant
Dim f As String
Dim n As Double
Set Rng = [myrange] 'This range object contains every property of the worksheet range
v = Rng.Value 'This variable will be an array contining the values from the range
n = v(4, 2) 'This number is an element of the array
f = Rng.Item(1).Formula 'The string f will contain the formula held in the first cell of the range
End Subwhich allow you to read the properties of range such as size and colours, return the values it contains or the formulas used to generate the values. As the named range moves the code follows.