Thread: Hidden Cells
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
ranswert ranswert is offline
external usenet poster
 
Posts: 161
Default Hidden Cells

I have the following code:

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
'MsgBox ("introws = " & intRows & vbLf & "intcols = " & intCols)
With Selection
Set rngAbove = .Cells(1, 1).Offset(-1, 0)
Set rngBelow = .Cells(1, 1).Offset(intRows, 0)
Set rngRight = .Cells(1, 1).Offset(0, intCols)
'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

before this runs I have:

Sub stopautocalc()
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
End Sub

It still runs a little slow. Is there a way to speed it up?
Thanks