101 Ready-To-Use Excel Macros

One of the most important things you do in Excel is navigating the worksheet. When you work with Excel manually, you are constantly navigating to appropriate ranges, finding the last row, moving to the last column, hiding and unhiding ranges, and so on. This all comes instinctively as part of doing work in Excel.

When you attempt to automate your work through VBA, you'll find that navigating your spreadsheet remains an important part of the automation process. In many cases, you need to dynamically navigate and manipulate Excel ranges, just as you would manually — only through VBA code. This chapter provides some of the most commonly used macros in terms of navigating and working with ranges.

The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.

Macro 31: Selecting and Formatting a Range

One of the basic things you need to do in VBA is to select a specific range to do something with it. This simple macro selects the range D5:D16.

How it works

In this macro, you explicitly define the range to select by using the Range object.

Sub Macro31a()

Range(“D5:D16”).Select

End Sub

After the range of cells is selected, you can use any of the Range properties to manipulate the cells. We've altered this macro so that the range is colored yellow, converted to number formatting, and bold.

Sub Macro31a()

Range(“D5:D16”).Select ...