Hide Sheet

[ranswert]

What is the best way to hide everything above, below and the the right of a
range of cells? The range will change depending on what is selected.
Thanks

 

[DomThePom]

Here's some code for you

Sub HideAroundSelection()
Dim intRows As Integer
Dim intCols As Integer
Dim rngAbove As Range
Dim rngRight As Range
Dim rngBelow As Range
Dim rngLeft As Range


intRows = Selection.Rows.Count
intCols = Selection.Columns.Count
With Selection
Set rngAbove = .Cells(1, 1)
Set rngBelow = .Cells(1, 1).Offset(intRows - 1, 0)
Set rngRight = .Cells(1, 1).Offset(0, intCols - 1)
Set rngLeft = .Cells(1, 1)

If rngAbove.Row <> 1 Then
Range(rngAbove.Offset(-1, 0), .Cells(1, 1).Offset((1 - .Cells(1,
1).Row))).EntireRow.Hidden = True
End If
If rngBelow.Row <> ActiveSheet.Rows.Count Then
Range(rngBelow.Offset(1, 0),
rngBelow.Offset(ActiveSheet.Rows.Count - rngBelow.Row)).EntireRow.Hidden =
True
End If
If rngRight.Column <> ActiveSheet.Columns.Count Then
Range(rngRight.Offset(0, 1), rngRight.Offset(0,
ActiveSheet.Columns.Count - rngRight.Column)).EntireColumn.Hidden = True
End If
If rngLeft.Column <> 1 Then
Range(rngLeft.Offset(0, -1), rngLeft.Offset(0, 1 -
rngLeft.Column)).EntireColumn.Hidden = True
End If

End With
Set rngAbove = Nothing
Set rngRight = Nothing
Set rngBelow = Nothing
Set rngLeft = Nothing


End Sub

 

[ranswert]

Thanks I'll give that a try

Here's some code for you

Sub HideAroundSelection()
Dim intRows As Integer
Dim intCols As Integer
Dim rngAbove As Range
Dim rngRight As Range
Dim rngBelow As Range
Dim rngLeft As Range


intRows = Selection.Rows.Count
intCols = Selection.Columns.Count
With Selection
Set rngAbove = .Cells(1, 1)
Set rngBelow = .Cells(1, 1).Offset(intRows - 1, 0)
Set rngRight = .Cells(1, 1).Offset(0, intCols - 1)
Set rngLeft = .Cells(1, 1)

If rngAbove.Row <> 1 Then
Range(rngAbove.Offset(-1, 0), .Cells(1, 1).Offset((1 - .Cells(1,
1).Row))).EntireRow.Hidden = True
End If
If rngBelow.Row <> ActiveSheet.Rows.Count Then
Range(rngBelow.Offset(1, 0),
rngBelow.Offset(ActiveSheet.Rows.Count - rngBelow.Row)).EntireRow.Hidden =
True
End If
If rngRight.Column <> ActiveSheet.Columns.Count Then
Range(rngRight.Offset(0, 1), rngRight.Offset(0,
ActiveSheet.Columns.Count - rngRight.Column)).EntireColumn.Hidden = True
End If
If rngLeft.Column <> 1 Then
Range(rngLeft.Offset(0, -1), rngLeft.Offset(0, 1 -
rngLeft.Column)).EntireColumn.Hidden = True
End If

End With
Set rngAbove = Nothing
Set rngRight = Nothing
Set rngBelow = Nothing
Set rngLeft = Nothing


End Sub

 

[ranswert]

That worked great, now how do I unhide the worksheet?

 

[DomThePom]

Sub UnHideAllRowsAndColumns()
With ActiveSheet.Cells
.EntireRow.Hidden = False
.EntireColumn.Hidden = False
End With
End Sub

To work this out yourself just record a macro and then change references to
selection