Contact Us

Contact SLIM Enterprises today to get the most out of your business.

captcha

How To Freeze A Specific Row In Excel Using VBA

November 2, 2017

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

Archive

Categories

Recent News

Recent Comments