You are here:

Excel/Moving sections including the vba code

Advertisement


Question
My worksheet is a work in process. I have decided that the layout of sections of cells would serve me better in a different place in the worksheet. There is a lot of VBA code directed at those cells so I can't just cut and paste like I would with cell formulas. Is there a way to move sections of cells and update the code to match the new location?

Answer
Hi Dave,

I apologize for my slow response to your question. I have been having a problem with my internet connection and didn't see your question until just now.

The best way in VBA to reference blocks of cells that could change locations is to use Named Ranges rather than A1-style cell references. So for example you could have a range of cells B5:D30, which you refer to in your code as Range("B5:D30"). And suppose you want to move this range to "E15:G40" without having to make a corresponding change to your code. You can simply give the range a name, say "Table1" (by selecting it and typing the name in the Name box above cell A1).  In your code you can then refer to this range as Range("Table1"). If you move the range by either dragging it to a new location or by cutting and pasting it, the range name will go with it and the code will refer to it at its new location.

Of course, one doesn't always want to refer to an entire range block of cells, but perhaps a cell within this range, and want this cell's reference in the code to continue to refer to the cell's relative location within the block of cells.  To do this simply write your code to refer to the cell by its relative location within the Named Range.  For example, suppose you have a reference in your code to cell C7, which is the cell in the second column and third row of B5:C30.  If you refer to this cell in your code using Range("Table1").Cells(3,2) it will then point to the correct cell (in this case F18) after the block is moved to E15:G40.

Once you have named the ranges that you might want to move, and made these modifications to your code you can move them at will and the correct cells will be referenced.  You can even move the range blocks to different sheets without adjusting the code.

Feel free to follow up if my explanation is not clear, and I will try to explain better.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.