Hiding rows and columns in a procedure

[Doo0592]

Hi all,

Can someone suggest how I can add code to the procedure below so that
all columns incuding j (and onwards) and all rows including 506 (and
onwards) will be hidden again at the end. I had thought that they would
stay hidden after the procedure is run but it just ends up mucking up
the row numbers...

Any help appreciated

Doo


Sub RMV_CASES_STORAGE()

ANS = MsgBox("This information is non-recoverable, have you copied it
to the STORAGE ARCHIVE FOLDER?", _
vbYesNo)

If ANS = vbNo Then
MsgBox ("Operation cancelled at your request."), vbOKOnly
Exit Sub

Else
MsgBox ("These cases will be deleted."), vbOKOnly

Rows("3:500").Select
Selection.Delete Shift:=xlUp
Range("B3:E500").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("B2:E2").Select
Range("E2").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
ActiveWindow.ScrollRow = 3
Range("B3:E12").Select
With Selection.Interior
.ColorIndex = 0
End With
Range("B501:E510").Select
With Selection.Interior
.ColorIndex = 40
End With
Range("B3").Select
End If


End Sub

 

[DaveyJones]

Wasn't sure if you wanted to unhide as well. So put unhide sub in too

Sub hde

Rows("506:65536").Select
Selection.EntireRow.Hidden = True
Columns("J:IV").Select
Selection.EntireColumn.Hidden = True
End Sub

Sub Unhde
Cells.Select
Selection.EntireColumn.Hidden = False
Selection.EntireRow.Hidden = False
End Sub

Just call them at the start and end of code( hde at end, unhde at start)
Hope that helps,..

 

[Jim Thomlinson]

Give this a try. I have cleaned up all of the extra stuff that is not
necessary...

Sub RMV_CASES_STORAGE()

If MsgBox("This information is non-recoverable, have you copied it to the "
& _
"STORAGE ARCHIVE FOLDER?", vbYesNo) = vbNo Then
MsgBox ("Operation cancelled at your request."), vbOKOnly

Else
MsgBox ("These cases will be deleted."), vbOKOnly

Rows("3:500").Delete Shift:=xlUp
Range("B3:E500").Borders.LineStyle = xlContinuous
Range("B2:E2").Borders.Weight = xlMedium
Range("B3:E12").Interior.ColorIndex = 0
Range("B501:E510").Interior.ColorIndex = 40
Range(Range("J1"), Cells(1, Columns.Count)).EntireColumn.Hidden = True
Range(Range("A506"), Cells(Rows.Count, "A")).EntireRow.Hidden = True
End If

End Sub

 

[NickHK]

Would it be sufficient to ;
Range("B3:E500").ClearContents
then you do not have reformat all those cells etc.

NickHK