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.
