Change/Focus Viewing Area

Change/Focus Viewing Area

When this macro is used it allows only the used area of the workbook to be viewed of accessed. It allows access to just one row or column to the right or below the used area, so as you add more data to the sheet it will expand the area. This macro should be loaded in the Worksheet Object of the workbook.

Here it is:

[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Change Focus”]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘Forces viewing area (used range +1) adjust by changing values below
Dim LastColumn As Integer
Dim lastRow As Long

If WorksheetFunction.CountA(Cells) > 0 Then
‘Search for any entry, by searching backwards by Rows.
lastRow = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
If lastRow <> 1048576 Then lastRow = lastRow + 1
‘Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
If LastColumn <> 16384 Then LastColumn = LastColumn + 1
Me.ScrollArea = Range(Cells(1, 1), Cells(lastRow, LastColumn)).Address
Else
Me.ScrollArea = ""
End If
End Sub
[/vb]