O'Reilly logo

101 Ready-To-Use Excel Macros by John Walkenbach, Michael Alexander

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

tip.eps 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required