Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making selected cells more visible | Excel Discussion (Misc queries) | |||
Excel document out of visible area | Excel Discussion (Misc queries) | |||
Selected cells not visible | Excel Discussion (Misc queries) | |||
I have charts that are only visible when selected, how do I fix? | Charts and Charting in Excel | |||
Constrain to visible area? | Excel Programming |