How To Freeze A Specific Row In Excel Using VBA

14 апреля, 2018

Articles

, , , ,

Whenever you have a large amount of data, it can be helpful to keep a specific row in sight when you scroll down the screen. While you can do this manually it makes sense to use VBA to select the row and “freeze” the pane.

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 which 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 is capable of holding large quantities of data, but it’s a good idea to keep the design of your spreadsheet as user-friendly as possible. By using the freeze panes command you can reduce errors and make life easier for anyone using your Excel file.

Похожие записи