Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Hiding rows and columns in a procedure
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 42
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,939
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 415
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Hiding rows and columns in XL 07 mlenard New Users to Excel 3 August 31st 09 05:42 PM
hiding columns with merged rows Cormac Excel Discussion (Misc queries) 3 June 5th 08 01:29 PM
Hiding rows and columns matelot Excel Programming 6 December 14th 05 06:56 PM
Hiding of rows and columns srinivasan Excel Discussion (Misc queries) 1 July 21st 05 08:59 AM
special type of rows to columns procedure cretesupplies New Users to Excel 3 June 3rd 05 06:28 PM


All times are GMT +1. The time now is 11:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement