Moving around the Worksheet in VBA for Excel

Using Named Fields, Variables and other Advanced Features

In this fifth part on moving around the worksheet are examples that you might never need, But when you need them they are hard to come around.

Special Addresses

If you name some fields (nfMyField) you must still use the quotation marks within the parentheses:
Range("nfMyField").Select

But if you use the content of a variable as address don't use the quotation marks:
varAddress = "A12"
Range(varAddress).Select

You can also use an address that you have stored in another cell of another sheet. For example let's say that in cell A1 of the sheet "Data" you have entered "C12". The following code:
Range(Sheets("Data").Range("A1").Value).Select
will take you to cell C12.

Use the same approach for Rows and Columns

If a block of cells is selected (like G5 to M25), this block of cells is the Selection and has adresses of its own within. For example:
Selection.Range("A1").Select will take you to cell G5
Selection.Range("D3").Select will take you to cell J7
Selection.Range("A1").Offset(1,1).Select will take you to cell H6

Special Formulas

Sometimes part of the formula must vary depending on which cell is selected. You need the line number of a formula to be variable. For example in cell G8 the formula must be "=F8", in cell G9 the formulas must be ""=F9". Try this code:
Activecell.Formula= "=F" & Activecell.Row
using an ampersand and replacing the row number by Activecell.Row. Notice also that the rest of the formula is encased in quotation marks.

If when the selected cell is G8 you want the formula to look on row up "=F7" you will use:
Activecell.Formula= "=F" & Activecell.Row - 1

The entire address can also vary. When in cell G8 the formula should be "=SUM(G1:G7)". You will write:
Activecell.Formula= "=SUM(G1:" & Activecell.Offset(-1,0).Address  & ")"

You can also make an address vary depending on the month:
Activecell.Formula= "=SUM(G1:G" & Month(Now)  & ")"

Advanced Examples on Ranges, Columns and Rows

You are adding lines of data to an excel database. You want each new record to be pasted in the first empty line. You first go to the title cell of the first column, you count the number of lines using a variable, you offset and you paste:
Range("A1").Select
Activecell.Offset(Activecell.CurrentRegion.Rows.Count,0).Select

You want to do the same thing from the cell that is selected:
Range(Activecell,Activecell.Offset(2,2)).Select
Note: No quotation marks around Activecell

If you want to develop an formula with an "If" to check if cell A1 is empty you would not write:
Range("B1").Formula= "=IF(A1="",1,0)" but
Range("B1").Formula= "=IF(A1="""",1,0)"
doubling the quotation marks within the firmula

You need a small VBA procedure to go down a column and delete the entire row if the cell is empty. You need a small macro to go down a column and delete the entire row if the value of the cell is 0. See both and 20 more of the same nature in "vba-short-macros-1.xls" one of the 25 spreadsheets that comes with the downloadable tutorial.

Discover more tips
and practical examples of real solutions to real problems
with step by step development instructions in the 25

Resource Spreadsheets