How to edit (or delete) macros

[kennethsharris]

I have set up macros in a large spreadsheet in order to go to a given cell in the spreadsheet. My given cell locations have changed as my spreadsheet has grown and I cannot find out how to edit the macros. Sorry if this is unclear. What am I missing please?

[Billy Spivy]

alt+11 to get into VBA

[dflak]

What Billy said plus - VBA does not know when you add or delete rows or columns in a spreadsheet, so code that looks at fixed cells will break when you do this.

One way around this is to give key cells a name. The simplest way to do this is go to the name box and type the name in. So let's say the cell you are interested in is Cell B6. Select cell B6, go to the name box (upper left near the ribbon) to the name box - chances are it says B6. Select this box and type in a descriptive name like Todays_Date and press ENTER. You have now named cell B6 on that sheet to Todays_Date. Unless you define another Todays_Date on another sheet, cell B6 on this sheet will be known as Todays_Date no matter where you are in the workbook. So you can write formulas using it on other sheets.

To get to it in VBA, use Range("Todays_Date"). Even if you add or delete columns or rows or even rename the sheet, Excel will keep track of where Todays_Date is. The only rule for names is that they must begin with a letter, they cannot contain spaces, the only punctuation allowed is the underscore and they can't resolve into a cell reference. For example END6 won't work because there is a cell with this address on the sheet. END_6 is OK.