The Best Way To Freeze A Specific Row In Stick out Using VBA

Programming

For those who have lots of data, it might be helpful to keep a specific row nearby if you scroll lower the screen. While you can do this by hands it appears sensible to utilize VBA to select the row and “freeze” the pane.

This article demonstrate the best way to freeze the most effective pane with regards to the volume of data you’ve.

A Typical Scenario Where The VBA Freeze Pane Command Is Useful

The instance we’ll use is a downloaded file that contains more than 100 records.

Whether it’s a customer or worker list where you need to “freeze” the most effective row when you scroll using the records.

The data might appear such as this:

Name

======

Emp1

Emp2

Emp3

.

.

Emp100

Emp101

We’ll assume you use VBA to save the data in to a new worksheet, rather of employing a handbook copy.

Whenever your code has finished installing the data, you may want to freeze the most effective row if there is more than 100 records.

First, you will have to exercise the amount of records you’ll find:

dim rng as range

dim records as extended

set rng=range(“a1”).currentRegion.posts(1)

records=rng.rows.count

Knowing the amount of rows will be in the data set you’ll be able to conditionally set the freeze pane command by selecting the row beneath the specified cell.

We will probably freeze the most effective row, but we will need to “unfreeze” any existing panes first.

activeWindow.freezepanes=false

if records >100 then

range(“a2”).activate

activeWindow.freezepanes=true

Finish If

If you wish to specify the row to get frozen you’ll be able to insert the following command inside the code.

myRow=x 1

range(“a” & x).activate

activeWindow.freezepanes=true

The identical technique could be familiar with set the freeze command based on other variables or criteria. Inside the example below, the code freezes the pane beneath the cell which contains “The month of the month of january 2013”.

set rng=range(“a1”).currentRegion.posts(1)

myCell=”The month of the month of january 2013″

For x = 1 To rng.Rows.Count

If rng.Rows(x) = str Then

myCell = rng.Rows(x).Offset(1, ).Address

Exit For

Finish If

Next

Range(myCell).Activate

ActiveWindow.freezePanes = True

Or, the code could locate 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, ).Address

Exit For

Finish If

Next

Range(myCell).Activate

ActiveWindow.freezePanes = True

You are able to activate the code frequently:

Produce a worksheet change event to acknowledge when the quantity of records exceeds a specific number

Write the code right into a current data import procedure

Create a tag for instance bold type or possibly a cell value to permit the code to acknowledge the best place to insert the frozen pane.

Summary

Stick out is able to do holding huge amounts of information, but it is wise to keep design for your spreadsheet as user-friendly as you can. While using freeze panes command you’ll be able to reduce errors making existence simpler for anyone utilizing your Stick out file.

Leave a Reply

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