ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Hiding rows and columns in a procedure (https://www.excelbanter.com/excel-programming/372182-hiding-rows-columns-procedure.html)

Doo0592
Hiding rows and columns in a procedure
 
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
Hiding rows and columns in a procedure
 
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,..


--
Dave


"Doo0592" wrote:

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

Jim Thomlinson
Hiding rows and columns in a procedure
 
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
--
HTH...

Jim Thomlinson


"Doo0592" wrote:

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

NickHK[_3_]
Hiding rows and columns in a procedure
 
Would it be sufficient to ;
Range("B3:E500").ClearContents
then you do not have reformat all those cells etc.

NickHK

"Doo0592"
roups.com...
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


All times are GMT +1. The time now is 11:59 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com