Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
PaulM
 
Posts: n/a
Default Hide / Unhide columns and rows

I want to create a command button in a worksheet that will hide/unhide each
alternate column from J - AG as well as row 4. Being new to VBA I've no idea
how this would look - or if it's even possible. Any ideas?
  #2   Report Post  
JulieD
 
Posts: n/a
Default

Hi Paul

with macros like these, the best place to start is with the macro recorder
(tools / macro / record new macro) - and record the actual steps that you
want the macro to perform) and then have a look at the code and tweak it
(tools / macro / macros - edit)

Cheers
JulieD

"PaulM" wrote in message
...
I want to create a command button in a worksheet that will hide/unhide each
alternate column from J - AG as well as row 4. Being new to VBA I've no
idea
how this would look - or if it's even possible. Any ideas?



  #3   Report Post  
swatsp0p
 
Posts: n/a
Default

For some reason, my post via Joseph Rubin's newsgroup did not make it to here.

Assuming your worksheet is nameed "Sheet1" (edit as needed), create your
button and add this code to your button: verify the columns listed match your
desired ones to hide!

-----------------------

Sub hidecolsrow4()
' hidecolsrow4 Macro
If Worksheets("Sheet1").Columns("J").Hidden = False Then
Range("J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,A D:AD,AF :AF,AH:AH").Select
Selection.EntireColumn.Hidden = True
Range("A4").Activate
Selection.EntireRow.Hidden = True
' unhidecolsrow4 Macro
Else
Rows("3:5").Select
Selection.EntireRow.Hidden = False
Columns("I:AI").Select
Selection.EntireColumn.Hidden = False
End If
Range("A1").Select
End Sub

----------------------------

If columns are hidden, this will Unhide them, if not hidden, it will Hide
them.

Good Luck
__________________
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA



  #4   Report Post  
PaulM
 
Posts: n/a
Default

That's great! Thanks, saved me a major headache!

"swatsp0p" wrote:

For some reason, my post via Joseph Rubin's newsgroup did not make it to here.

Assuming your worksheet is nameed "Sheet1" (edit as needed), create your
button and add this code to your button: verify the columns listed match your
desired ones to hide!

-----------------------

Sub hidecolsrow4()
' hidecolsrow4 Macro
If Worksheets("Sheet1").Columns("J").Hidden = False Then
Range("J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,A D:AD,AF :AF,AH:AH").Select
Selection.EntireColumn.Hidden = True
Range("A4").Activate
Selection.EntireRow.Hidden = True
' unhidecolsrow4 Macro
Else
Rows("3:5").Select
Selection.EntireRow.Hidden = False
Columns("I:AI").Select
Selection.EntireColumn.Hidden = False
End If
Range("A1").Select
End Sub

----------------------------

If columns are hidden, this will Unhide them, if not hidden, it will Hide
them.

Good Luck
__________________
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA



  #5   Report Post  
Posted to microsoft.public.excel.misc
Al
 
Posts: n/a
Default Hide / Unhide columns and rows

Great...I have a similar objective, and I wonder if you could advise the
appropriate solution? I'd like to be able to hide rows or columns if a
specific cell has no data.

My spreadsheet is a list of names and hours and charges, but if name X has
zero hours - i.e. hours X = 0 - then I do not need the row, otherwise my
spreadsheet will have so many unused rows, and so want to hide it. To
complicate matters, I want the rows that are the various titles to remain
unhidden, so I see something that only conisders the data rows and hides them
if the hours = zero.

Allan
"swatsp0p" wrote:

For some reason, my post via Joseph Rubin's newsgroup did not make it to here.

Assuming your worksheet is nameed "Sheet1" (edit as needed), create your
button and add this code to your button: verify the columns listed match your
desired ones to hide!

-----------------------

Sub hidecolsrow4()
' hidecolsrow4 Macro
If Worksheets("Sheet1").Columns("J").Hidden = False Then
Range("J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,A D:AD,AF :AF,AH:AH").Select
Selection.EntireColumn.Hidden = True
Range("A4").Activate
Selection.EntireRow.Hidden = True
' unhidecolsrow4 Macro
Else
Rows("3:5").Select
Selection.EntireRow.Hidden = False
Columns("I:AI").Select
Selection.EntireColumn.Hidden = False
End If
Range("A1").Select
End Sub

----------------------------

If columns are hidden, this will Unhide them, if not hidden, it will Hide
them.

Good Luck
__________________
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA





  #6   Report Post  
Posted to microsoft.public.excel.misc
ScottO
 
Posts: n/a
Default Hide / Unhide columns and rows

Have you tried Auto Filter in the Data menu?
Rgds,
ScottO

"Al" wrote in message
...
| Great...I have a similar objective, and I wonder if you could
advise the
| appropriate solution? I'd like to be able to hide rows or columns
if a
| specific cell has no data.
|
| My spreadsheet is a list of names and hours and charges, but if
name X has
| zero hours - i.e. hours X = 0 - then I do not need the row,
otherwise my
| spreadsheet will have so many unused rows, and so want to hide it.
To
| complicate matters, I want the rows that are the various titles to
remain
| unhidden, so I see something that only conisders the data rows and
hides them
| if the hours = zero.
|
| Allan
| "swatsp0p" wrote:
|
| For some reason, my post via Joseph Rubin's newsgroup did not
make it to here.
|
| Assuming your worksheet is nameed "Sheet1" (edit as needed),
create your
| button and add this code to your button: verify the columns
listed match your
| desired ones to hide!
|
| -----------------------
|
| Sub hidecolsrow4()
| ' hidecolsrow4 Macro
| If Worksheets("Sheet1").Columns("J").Hidden = False Then
| Range("J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,A D:AD,AF
:AF,AH:AH").Select
| Selection.EntireColumn.Hidden = True
| Range("A4").Activate
| Selection.EntireRow.Hidden = True
| ' unhidecolsrow4 Macro
| Else
| Rows("3:5").Select
| Selection.EntireRow.Hidden = False
| Columns("I:AI").Select
| Selection.EntireColumn.Hidden = False
| End If
| Range("A1").Select
| End Sub
|
| ----------------------------
|
| If columns are hidden, this will Unhide them, if not hidden, it
will Hide
| them.
|
| Good Luck
| __________________
| Bruce
| The older I get, the better I used to be.
| Minneapolis, MN USA
|
|
|


  #7   Report Post  
Posted to microsoft.public.excel.misc
Al
 
Posts: n/a
Default Hide / Unhide columns and rows

haven't...don't know what this is, but I will read up on this....thanks for
the tip! Any pointers?

"ScottO" wrote:

Have you tried Auto Filter in the Data menu?
Rgds,
ScottO

"Al" wrote in message
...
| Great...I have a similar objective, and I wonder if you could
advise the
| appropriate solution? I'd like to be able to hide rows or columns
if a
| specific cell has no data.
|
| My spreadsheet is a list of names and hours and charges, but if
name X has
| zero hours - i.e. hours X = 0 - then I do not need the row,
otherwise my
| spreadsheet will have so many unused rows, and so want to hide it.
To
| complicate matters, I want the rows that are the various titles to
remain
| unhidden, so I see something that only conisders the data rows and
hides them
| if the hours = zero.
|
| Allan
| "swatsp0p" wrote:
|
| For some reason, my post via Joseph Rubin's newsgroup did not
make it to here.
|
| Assuming your worksheet is nameed "Sheet1" (edit as needed),
create your
| button and add this code to your button: verify the columns
listed match your
| desired ones to hide!
|
| -----------------------
|
| Sub hidecolsrow4()
| ' hidecolsrow4 Macro
| If Worksheets("Sheet1").Columns("J").Hidden = False Then
| Range("J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,A D:AD,AF
:AF,AH:AH").Select
| Selection.EntireColumn.Hidden = True
| Range("A4").Activate
| Selection.EntireRow.Hidden = True
| ' unhidecolsrow4 Macro
| Else
| Rows("3:5").Select
| Selection.EntireRow.Hidden = False
| Columns("I:AI").Select
| Selection.EntireColumn.Hidden = False
| End If
| Range("A1").Select
| End Sub
|
| ----------------------------
|
| If columns are hidden, this will Unhide them, if not hidden, it
will Hide
| them.
|
| Good Luck
| __________________
| Bruce
| The older I get, the better I used to be.
| Minneapolis, MN USA
|
|
|



  #8   Report Post  
Posted to microsoft.public.excel.misc
ScottO
 
Posts: n/a
Default Hide / Unhide columns and rows

Just search Auto Filter in Excel help and all will be revealed.
S

"Al" wrote in message
...
| haven't...don't know what this is, but I will read up on
this....thanks for
| the tip! Any pointers?
|
| "ScottO" wrote:
|
| Have you tried Auto Filter in the Data menu?
| Rgds,
| ScottO
|
| "Al" wrote in message
| ...
| | Great...I have a similar objective, and I wonder if you could
| advise the
| | appropriate solution? I'd like to be able to hide rows or
columns
| if a
| | specific cell has no data.
| |
| | My spreadsheet is a list of names and hours and charges, but if
| name X has
| | zero hours - i.e. hours X = 0 - then I do not need the row,
| otherwise my
| | spreadsheet will have so many unused rows, and so want to hide
it.
| To
| | complicate matters, I want the rows that are the various titles
to
| remain
| | unhidden, so I see something that only conisders the data rows
and
| hides them
| | if the hours = zero.
| |
| | Allan
| | "swatsp0p" wrote:
| |
| | For some reason, my post via Joseph Rubin's newsgroup did not
| make it to here.
| |
| | Assuming your worksheet is nameed "Sheet1" (edit as needed),
| create your
| | button and add this code to your button: verify the columns
| listed match your
| | desired ones to hide!
| |
| | -----------------------
| |
| | Sub hidecolsrow4()
| | ' hidecolsrow4 Macro
| | If Worksheets("Sheet1").Columns("J").Hidden = False Then
| | Range("J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,A D:AD,AF
| :AF,AH:AH").Select
| | Selection.EntireColumn.Hidden = True
| | Range("A4").Activate
| | Selection.EntireRow.Hidden = True
| | ' unhidecolsrow4 Macro
| | Else
| | Rows("3:5").Select
| | Selection.EntireRow.Hidden = False
| | Columns("I:AI").Select
| | Selection.EntireColumn.Hidden = False
| | End If
| | Range("A1").Select
| | End Sub
| |
| | ----------------------------
| |
| | If columns are hidden, this will Unhide them, if not hidden,
it
| will Hide
| | them.
| |
| | Good Luck
| | __________________
| | Bruce
| | The older I get, the better I used to be.
| | Minneapolis, MN USA
| |
| |
| |
|
|
|


  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10
Default Hide / Unhide columns and rows

I am trying to do something similar. I would like to have rows 37 through 86
hidden and use a command button to unhide the next 5 rows each time it is
clicked. For example, the first time the command button is clicked rows 37,
38, 39,40, and 41 are unhiden and 42 through 86 remain hiden. The second
time the command button is clicked 42, 43, 44, 45. and 46 are unhiden and so
on. Is it possible to click the button multiple times?

"swatsp0p" wrote:

For some reason, my post via Joseph Rubin's newsgroup did not make it to here.

Assuming your worksheet is nameed "Sheet1" (edit as needed), create your
button and add this code to your button: verify the columns listed match your
desired ones to hide!

-----------------------

Sub hidecolsrow4()
' hidecolsrow4 Macro
If Worksheets("Sheet1").Columns("J").Hidden = False Then
Range("J:J,L:L,N:N,P:P,R:R,T:T,V:V,X:X,Z:Z,AB:AB,A D:AD,AF :AF,AH:AH").Select
Selection.EntireColumn.Hidden = True
Range("A4").Activate
Selection.EntireRow.Hidden = True
' unhidecolsrow4 Macro
Else
Rows("3:5").Select
Selection.EntireRow.Hidden = False
Columns("I:AI").Select
Selection.EntireColumn.Hidden = False
End If
Range("A1").Select
End Sub

----------------------------

If columns are hidden, this will Unhide them, if not hidden, it will Hide
them.

Good Luck
__________________
Bruce
The older I get, the better I used to be.
Minneapolis, MN USA



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
how to hide rows in a protected sheet Prakash Excel Worksheet Functions 7 January 18th 05 03:42 PM
Convert multiple columns to rows Lois Lane Excel Worksheet Functions 8 January 10th 05 01:47 AM
Unhide rows [email protected] Excel Discussion (Misc queries) 3 December 14th 04 06:28 PM
interchange columns with rows Herman Excel Discussion (Misc queries) 2 December 8th 04 06:40 PM
Checkbox to hide and unhide rows Please. Steved Excel Worksheet Functions 2 December 7th 04 12:30 AM


All times are GMT +1. The time now is 06:53 AM.

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"