How to auto update the macro cell reference when new rows are added or deleted.

[actualize]

When I Insert A Row My Excel Macro Don't work

Hi,

I'm very green to using macros. I have created 2 simple macro buttons, one to clear contents “” in specific cells in a column
Sub CLEAR_Click()
Range("C15").Value = ""
Range("C17").Value = ""
Etc.
Range("C374").Value = ""
MsgBox "Pricelist Cleared"
End Sub
and another macro button to populate those same cells with the “1”.
Sub RESET_Click()
Range("C15").Value = "1"
Range("C17").Value = "1"
Etc.
Range("C374").Value = "1"
MsgBox "Pricelist Reset"
End Sub

There are hundreds of rows and some rows are skipped this is why I had to reference each cell individually instead of a range. I always need to add rows to the spreadsheet, this means I am constantly updating the Cell References the Macro refers to.

Since I am so green I haven’t a clue how to begin. I’ve seen the:
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

But I’m unsure how/where to add it and inserting this doesn’t help.

I’ve seen other elaborate methods like Dynamic Name Ranges or Range("MyName") but without any hard examples or any intelligible ‘how to’ examples.

It is also not feasible to add content in another column.

It’s a nightmare because every time a new product is added all the macro's need updating. I know that the formulas update automatically when rows or columns are added. Why can't macros!?

Can anyone please include the correct LastRow or Name Range etc? I just seem to have enough knowledge to be dangerous.

Many Thanks.

Trenton

[Greg M]

Hi there,

As usual, there is more than one way to approach this requirement and the most suitable way will depend on your workbook/worksheet layout.

Can you post a sample of your workbook here? We don't need to see meaningful data, just how the workbook and worksheets are laid out. Also, can you give us an idea of where new data (product?) rows are added - are they all in a single block or are there empty rows between data rows?

Regards,

Greg M