How To Freeze A Specific Row In Excel Using VBA

How To Freeze A Specific Row In Excel Using VBA

Each time you will have a considerable amount of knowledge, it may be useful to maintain a selected row in sight whenever you scroll down the display. Whereas you are able to do this manually it is sensible to make use of VBA to pick the row and “freeze” the pane.

This text will present you how one can freeze the highest pane relying on the quantity of knowledge you will have.

A Typical Situation The place The VBA Freeze Pane Command Is Helpful

The instance we’ll use is a downloaded file which comprises greater than 100 entries.

It might be a buyer or worker checklist the place you wish to “freeze” the highest row as you scroll via the entries.

The information may seem like this:


Title
======
Emp1
Emp2
Emp3
.
.
Emp100
Emp101

We’ll assume you are utilizing VBA to save lots of the information into a brand new worksheet, moderately than utilizing a handbook copy and paste.

As soon as your code has completed downloading the information, you may wish to freeze the highest row if there are greater than 100 entries.

First, you may have to work out what number of entries there are:


dim rng as vary
dim entries as lengthy
set rng=vary("a1").currentRegion.columns(1)
entries=rng.rows.rely

As soon as you understand how many rows are within the knowledge set you’ll be able to conditionally set the freeze pane command by deciding on the row beneath the required cell.

We will freeze the highest row, however we might want to “unfreeze” any current panes first.


activeWindow.freezepanes=false
if entries >100 then

vary("a2").activate

activeWindow.freezepanes=true
Finish If

If it is advisable specify the row to be frozen you’ll be able to insert the next command within the code.


myRow=x+1
vary("a" & x).activate
activeWindow.freezepanes=true

The identical approach might be used to set the freeze command primarily based on different variables or standards. Within the instance beneath, the code freezes the pane beneath the cell containing “January 2013”.


set rng=vary("a1").currentRegion.columns(1)
myCell="January 2013"

For x = 1 To rng.Rows.Rely

If rng.Rows(x) = str Then

myCell = rng.Rows(x).Offset(1, zero).Handle

Exit For

Finish If
Subsequent
Vary(myCell).Activate
ActiveWindow.freezePanes = True

Or, the code might seek for a cell with daring sort.


For x = 1 To rng.Rows.Rely

If rng.Rows(x).font.daring=true Then

myCell = rng.Rows(x).Offset(1, zero).Handle

Exit For

Finish If
Subsequent
Vary(myCell).Activate
ActiveWindow.freezePanes = True

You could possibly activate the code in a number of methods:

  • Arrange a worksheet change occasion to determine when the variety of entries exceeds a sure quantity
  • Write the code into an current knowledge import process
  • Create a tag akin to daring sort or a cell worth to allow the code to determine the right place to insert the frozen pane.

Abstract

Excel is able to holding massive portions of knowledge, however it’s a good suggestion to maintain the design of your spreadsheet as user-friendly as doable. By utilizing the freeze panes command you’ll be able to scale back errors and make life simpler for anybody utilizing your Excel file.

No Comments

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.