Using VB to hide sections of spreadsheet

[Dark0Prince]

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]

Hi Dark0Prince,

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

Cheers,
Alan.

 

[Dark0Prince]

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]

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

In a standard module:

Public Sub Test()

ActiveCell.CurrentRegion.Interior.ColorIndex = 3

End Sub

 

[Alan_P]

Hi Dark0Prince,

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

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]

Hi Dark0Prince,

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

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]

Ah right ok, this should do the trick for that

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.