There are times when you may want to keep a specific row visible at the top or bottom of your worksheet while you scroll through the rest of the data.
Whenever you have a large amount of data, keeping a specific row in sight when you scroll down the screen can be helpful. While you can do this manually, using VBA to select the row and “freeze” the pane makes sense.
This article will show you how to freeze the top pane depending on the amount of data you have.
A Typical Scenario Where The VBA Freeze Pane Command Is Useful
The example we’ll use is a downloaded file that contains more than 100 entries.
It could be a customer or employee list where you would like to “freeze” the top row as you scroll through the entries.
The data might look like this:
Name ====== Emp1 Emp2 Emp3 . . Emp100 Emp101
We’ll assume you’re using VBA to save the data into a new worksheet, rather than using a manual copy and paste.
Once your code has finished downloading the data, you might want to freeze the top row if there are more than 100 entries.
First, you’ll need to work out how many entries there are:
dim rng as range
dim entries as long
set rng=range("a1").currentRegion.columns(1)
entries=rng.rows.count
Once you know how many rows are in the data set you can conditionally set the freeze pane command by selecting the row below the specified cell.
We’re going to freeze the top row, but we will need to “unfreeze” any existing panes first.
activeWindow.freezepanes=false if entries >100 then
range(“a2”).activate
activeWindow.freezepanes=true
End If
If you need to specify the row to be frozen you can insert the following command in the code.
myRow=x+1
range("a" & x).activate
activeWindow.freezepanes=true
The same technique could be used to set the freeze command based on other variables or criteria. In the example below, the code freezes the pane below the cell containing “January 2013”.
set rng=range("a1").currentRegion.columns(1)
myCell="January 2013"
For x = 1 To rng.Rows.Count
If rng.Rows(x) = str Then
myCell = rng.Rows(x).Offset(1, 0).Address
Exit For
End If
Next
Range(myCell).Activate
ActiveWindow.freezePanes = True
Or, the code could search for a cell with bold type.
For x = 1 To rng.Rows.Count
If rng.Rows(x).font.bold=true Then
myCell = rng.Rows(x).Offset(1, 0).Address
Exit For
End If
Next
Range(myCell).Activate
ActiveWindow.freezePanes = True
You could activate the code in several ways:
- Set up a worksheet change event to identify when the number of entries exceeds a certain number
- Write the code into an existing data import procedure
- Create a tag such as bold type or a cell value to enable the code to identify the correct place to insert the frozen pane.
Summary
Excel can hold large quantities of data, but it’s a good idea to keep the design of your spreadsheet as user-friendly as possible. Using the freeze panes command, you can reduce errors and make life easier for anyone using your Excel file.
