Relative Recording
When recording using relative referencing you are recording movement (the number of rows or columns you have moved) from your original position. This works well if you need to perform the same set of actions on different rows and columns in your data. Record it once and then apply the macro to each row or column. Because you are using relative references and not recording absolute position such as row 1 or column 3, the macro won’t keep applying the actions to the same part of the data.
Shortcut keys for Navigating and Selecting Data Regions
When you record a macro to data say 10 rows high and 4 columns wide, the macro records the number of rows and columns you are applying your actions to. This works fine if the data is always going to be the same size. The macro won’t work so well on a different sized set of data. That is unless you use shortcut keys that allow you to navigate or select to the edge of a data region.
CTRL and the arrow keys (up, down, left, right) navigate to the edges of a data region. So by recording this you will always move to the last or first consecutive value in your data.
CTRL SHIFT and the arrow keys select to the edge of a data region. So however big your data range is you will always select to the last or first piece of data.
Performing Calculations in a Macro
If you want to use a function such as SUM that requires a range of cell to perform a calculation on, make sure you fix the first cell address in the range. By doing this the formula will work on different sized ranges.
For example =SUM($A$1:A10) will work if the range is larger or smaller than A1 to A10
Adding Code to Allow You to Loop Through a Range of Cells
If you need to perform the same set of action on all the rows or columns in your data you can loop through your cells using a bit of VBA code.
Do While ActiveCell.Value <> Empty
Your code
Loop
By placing these two lines of code above and below your code, the code will run again and again until it reaches an empty cell.