VBA - Only Selected area visible
 

Excel VBA programming : How to make only a selected sheet area visible,
e.g. the row 12 until the end shall be invisible and the column X until
the end shall be invisible. The problem may be that different Excel
versions have different max rows and columns. And how to adjust the
scrollbars to that selected area. What if the selected area is smaller
than the usable width of the Excel spreadsheet. The function of sizing
the width and height will not work ?


---
Message posted from http://www.ExcelForum.com/

VBA - Only Selected area visible
 

Hi Geo,

First, you would need to "zoom in" on that range, which you can do by
selecting the range and using ActiveWindow.Zoom=True. Next, you would want
to hide the rest of the rows/columns from view. Here's a quick example:

Sub test()
'/ zoom in on desired range
Application.Goto Sheet1.Range("A1:X12")
ActiveWindow.Zoom = True
'/ hide rest of cells
With Sheet1
.Range(.Range("Y1"), .Range("X1").End(xlToRight) _
).EntireColumn.Hidden = True
.Range(.Range("A13"), .Range("A13").End(xlDown) _
).EntireRow.Hidden = True
End With
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Excel VBA programming : How to make only a selected sheet area
visible, e.g. the row 12 until the end shall be invisible and the
column X until the end shall be invisible. The problem may be that
different Excel versions have different max rows and columns. And how
to adjust the scrollbars to that selected area. What if the selected
area is smaller than the usable width of the Excel spreadsheet. The
function of sizing the width and height will not work ?


---
Message posted from http://www.ExcelForum.com/

VBA - Only Selected area visible
 

Hey

It could be done like this:

Public Sub AutoResizeView()
Dim rView As Range

Set rView = Selection

With ActiveSheet
' Show all
.Columns("A:IV").Hidden = False
.Rows("1:65536").Hidden = False

' Hide not selected
If Not (rView.Columns(1).Column = 1) Then
.Range(.Cells(1, 1), .Cells(1, rView.Columns(1).Column -
1)).EntireColumn.Hidden = True
End If
If Not (rView.Columns(rView.Columns.Count).Column = 256) Then
.Range(.Cells(1, rView.Columns(rView.Columns.Count + 1).Column),
Cells(1, 256)).EntireColumn.Hidden = True
End If
If Not (rView.Rows(1).Row = 1) Then
.Range(.Cells(1, 1), .Cells(rView.Rows(1).Row - 1,
1)).EntireRow.Hidden = True
End If
If Not (rView.Rows(rView.Rows.Count).Row = 65536) Then
.Range(.Cells(rView.Rows(rView.Rows.Count).Row + 1, 1),
..Cells(65536, 1)).EntireRow.Hidden = True
End If
End With

' Zoom
ActiveWindow.Zoom = True

Set rView = Nothing
End Sub


Cheers,
Flemming


"Geo Siggy " wrote in message
...
Excel VBA programming : How to make only a selected sheet area visible,
e.g. the row 12 until the end shall be invisible and the column X until
the end shall be invisible. The problem may be that different Excel
versions have different max rows and columns. And how to adjust the
scrollbars to that selected area. What if the selected area is smaller
than the usable width of the Excel spreadsheet. The function of sizing
the width and height will not work ?


---
Message posted from http://www.ExcelForum.com/