Using VB to hide sections of spreadsheet

Dark0Prince

Active Member
I want to be able to hide sections of my spreadsheet. From let's say the GJ section to the end of the GJ section. But I don't know how to define the end of the GJ section because new data keeps moving the rows down and changing how big the section is. This code below works but as soon as I insert another row this will not grab the whole section anymore because it is now bigger. Is there a way I can define the end of the section. I'm using command buttons for this code

Sub GJ()
'
' GJ Macro
' UNSHRINKS ALL SECTIONS>HIDES ALL SECTIONS THAT YOU DON'T WANT TO SEE
'

'
Cells.Select
Selection.EntireRow.Hidden = False
Rows("2:2").Select
ActiveWindow.SmallScroll Down:=519
Rows("2:539").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=309
Rows("861:861").Select
ActiveWindow.SmallScroll Down:=1143
Rows("861:2000").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-465
Range("A1").Select
End Sub
 

Alan_P

Well-known Member
Hi Dark0Prince,

You'll have to tell us what defines the start and end of the 'GJ' section?

Cheers,
Alan.
 

Dark0Prince

Active Member
Thanks for the quick response well I could define them with a cell that contains the text startGJ and endGJ and then I could just hide the text in the cells.
 

Gary McMaster

Well-known Member
If it's a contiguous block of cells the "CurrentRegion" property may work.

In a standard module:
Code:
Public Sub Test()

ActiveCell.CurrentRegion.Interior.ColorIndex = 3

End Sub
 

Alan_P

Well-known Member
Hi Dark0Prince,

This will hide everything between the cell where it finds startGJ and the cell where it finds endGJ

Code:
Sub HideRowsBetween()

SRow = Cells.Find("startGJ").Row
ERow = Cells.Find("endGJ").Row
Rows(SRow & ":" & ERow).EntireRow.Hidden = True

End Sub
Hope this helps,
Cheers,
Alan.
 

Dark0Prince

Active Member
Hi Dark0Prince,

This will hide everything between the cell where it finds startGJ and the cell where it finds endGJ

Code:
Sub HideRowsBetween()

SRow = Cells.Find("startGJ").Row
ERow = Cells.Find("endGJ").Row
Rows(SRow & ":" & ERow).EntireRow.Hidden = True

End Sub
Hope this helps,
Cheers,
Alan.
I wanted to hide everything but those range of rows. I may have said it in reverse I'm sorry if I did.
 

Alan_P

Well-known Member
Ah right ok, this should do the trick for that

Code:
Sub HideRowsOutside()

LRow = Range("[B][COLOR=#FF0000]A[/COLOR][/B]" & Rows.Count).End(xlUp).Row
SRow = Cells.Find("startGJ").Row
ERow = Cells.Find("endGJ").Row

Rows([B][COLOR=#0000FF]1[/COLOR][/B] & ":" & SRow).EntireRow.Hidden = True
Rows(ERow & ":" & LRow).EntireRow.Hidden = True

End Sub
This assumes you have data in Column A, if not change the red 'A' to a column you will have data in. Also if you have a header row that you want to keep change the blue '1' to '2' to keep the first row (or however many rows you would like to keep...)

Cheers,
Alan.
 

Some videos you may like

This Week's Hot Topics

Top