Hide/Unhide groups of cells in excel with Button -

i have group of cells b32 till r32 length , b32 b51 breadth. want keep block hidden @ start when sheet opened. have named block 'analysis'.

there button in sheet. when button pressed, want unhide block. new excel vba. know syntax/code doing operation.

thanks in advance.

ananda

you cant hide area mattcrum has mentioned.
have 3 choices far concerned

---

now, make sure have something(data - not empty cells) in range 32:51 , main sheet either called sheet1 or change sheet1 in code suit worksheets name

1) in vbe ( visual basic editor ) double click thisworkbook in project explorer , paste code

private sub workbook_open()     thisworkbook.sheets("sheet1").rows(32 & ":" & 51).hidden = true end sub 

right click on folder modules , insert new module, paste code

sub unhide()     thisworkbook.sheets("sheet1").rows(32 & ":" & 51).hidden = false end sub 

now, add button on spreadsheet, right click , assign macro called unhide it.

save changes , save workbook *.xlsm file

notice when open workbook now, rows 32 51 hidden. clicking button unhide them.

---

2) can change font color white "hide" contents.

follow step 1, , replace

thisworkbook.sheets("sheet1").rows(32 & ":" & 51).hidden = true 

with

thisworkbook.sheets("sheet1").range("b32:r51").font.color = rgb(255, 255, 255) 

and code in module ( unhide subroutine )with

thisworkbook.sheets("sheet1").range("b32:r51").font.color = rgb(0, 0, 0) 

now, works similar step 1 except "hiding"(changing) font color instead of hiding rows. not great approach, if works cool

---

3) follow step 1 , replace code under thisworkbook with

option explicit  private sub workbook_open()      application.screenupdating = false      set ws = thisworkbook.sheets("sheet1")      worksheets.add(after:=worksheets(worksheets.count)).name = "hiddensheet"     set hs = thisworkbook.sheets(worksheets.count)     hs.visible = xlsheethidden      ws.range("b32:r51").select     selection.copy      hs         .activate         .range("b32").select         selection.pastespecial paste:=xlpastevalues, operation:=xlnone, skipblanks _             :=false, transpose:=false         application.cutcopymode = false     end      ws.activate     ws.rows(32 & ":" & 51).delete      application.screenupdating = true end sub  private sub workbook_beforeclose(cancel boolean)     call deletehidden(worksheets.count) end sub 

and code in module1 with

option explicit public ws worksheet, hs worksheet  sub unhide()     hs         .activate         .rows("32:51").select         selection.copy     end     ws         .activate         .rows("32:32").select         selection.insert shift:=xldown     end end sub  sub deletehidden(num&)     application.displayalerts = false     worksheets(num).delete     application.displayalerts = true     set hs = nothing end sub