Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide rows
autofilter doesnt do the trick for me so i'm thinking maybe a macro will.
I made a template(sort've) and i want to specify how many rows to hide, before i even start (so i'm not looking at rows that i wont be using, or accidently typing stuff in them when i didnt mean to) (the problem is, i dont know the first thing about writing one) This is what i have in the following cells (and one way i could think of doing what i want): (NOTE: by "[__]" i just mean its an empty cell, which needs to be filled in manually with a number) Column A Column B A1: [__] B1: "is the # of rows i'll be using in the Agroup (cells A25 - A44)" A2: [__] B2: "is the # of rows i'll be using in the Bgroup (cells A45 - A64)" (this pattern goes on all the way to the Ogroup (cells A305 through A324) An example of what i want to happen: If i put "5" in A1 (which is currently an empty cell), the macro would automatically unhide the first 5 rows of of the Agroup (cells A25 through A30) -- if they aren't unhidden already. and then the macro would hide the rest of the rows in the Agroup (cells A31 through A44), if they aren't hidden already. The same thing would happen for the Bgroup if i typed a number in A2 the range i can enter should be between 1 and 19 (either nothing should happen if a lower/higher number is entered, or preferrably, a warning of some sort, asking the user to "input a number between 1 and 20 please") thanks in advance if you can help me out P.S. i know i can manually hide these rows.. but i may share this template with people who dont know the very much about excel, or dont want to put too much effort into manually hiding the rows. the point of the template is to save time) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide rows
Try:
Dim lngAGroup As Long, lngBGroup As Long 'etc lngAGroup = 21 lngBGroup = 45 'etc Rows(lngAGroup & ":" & lngAGroup + [A1] - 1).Hidden = True Rows(lngBGroup & ":" & lngBGroup + [A2] - 1).Hidden = True 'etc It's worth using data validation to restrict the values that can be entered in Cells A1 & A2 etc. HTH Roger Shaftesbury (UK) "Mo2" wrote in message ... autofilter doesnt do the trick for me so i'm thinking maybe a macro will. I made a template(sort've) and i want to specify how many rows to hide, before i even start (so i'm not looking at rows that i wont be using, or accidently typing stuff in them when i didnt mean to) (the problem is, i dont know the first thing about writing one) This is what i have in the following cells (and one way i could think of doing what i want): (NOTE: by "[__]" i just mean its an empty cell, which needs to be filled in manually with a number) Column A Column B A1: [__] B1: "is the # of rows i'll be using in the Agroup (cells A25 - A44)" A2: [__] B2: "is the # of rows i'll be using in the Bgroup (cells A45 - A64)" (this pattern goes on all the way to the Ogroup (cells A305 through A324) An example of what i want to happen: If i put "5" in A1 (which is currently an empty cell), the macro would automatically unhide the first 5 rows of of the Agroup (cells A25 through A30) -- if they aren't unhidden already. and then the macro would hide the rest of the rows in the Agroup (cells A31 through A44), if they aren't hidden already. The same thing would happen for the Bgroup if i typed a number in A2 the range i can enter should be between 1 and 19 (either nothing should happen if a lower/higher number is entered, or preferrably, a warning of some sort, asking the user to "input a number between 1 and 20 please") thanks in advance if you can help me out P.S. i know i can manually hide these rows.. but i may share this template with people who dont know the very much about excel, or dont want to put too much effort into manually hiding the rows. the point of the template is to save time) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to hide rows
sorry , i dont understand..
am i supposed to right click on the SHEET tab - View code, then paste it in the white area? if so, it didnt work. nothing happpened when i messed with A1's/A2's value and so on. if i hit the RUN/PLAY button , it asks me which macro to run, and there were none in the list. "Roger Whitehead" wrote: Try: Dim lngAGroup As Long, lngBGroup As Long 'etc lngAGroup = 21 lngBGroup = 45 'etc Rows(lngAGroup & ":" & lngAGroup + [A1] - 1).Hidden = True Rows(lngBGroup & ":" & lngBGroup + [A2] - 1).Hidden = True 'etc It's worth using data validation to restrict the values that can be entered in Cells A1 & A2 etc. HTH Roger Shaftesbury (UK) "Mo2" wrote in message ... autofilter doesnt do the trick for me so i'm thinking maybe a macro will. I made a template(sort've) and i want to specify how many rows to hide, before i even start (so i'm not looking at rows that i wont be using, or accidently typing stuff in them when i didnt mean to) (the problem is, i dont know the first thing about writing one) This is what i have in the following cells (and one way i could think of doing what i want): (NOTE: by "[__]" i just mean its an empty cell, which needs to be filled in manually with a number) Column A Column B A1: [__] B1: "is the # of rows i'll be using in the Agroup (cells A25 - A44)" A2: [__] B2: "is the # of rows i'll be using in the Bgroup (cells A45 - A64)" (this pattern goes on all the way to the Ogroup (cells A305 through A324) An example of what i want to happen: If i put "5" in A1 (which is currently an empty cell), the macro would automatically unhide the first 5 rows of of the Agroup (cells A25 through A30) -- if they aren't unhidden already. and then the macro would hide the rest of the rows in the Agroup (cells A31 through A44), if they aren't hidden already. The same thing would happen for the Bgroup if i typed a number in A2 the range i can enter should be between 1 and 19 (either nothing should happen if a lower/higher number is entered, or preferrably, a warning of some sort, asking the user to "input a number between 1 and 20 please") thanks in advance if you can help me out P.S. i know i can manually hide these rows.. but i may share this template with people who dont know the very much about excel, or dont want to put too much effort into manually hiding the rows. the point of the template is to save time) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Macro to hide rows | Excel Discussion (Misc queries) | |||
macro to hide rows | Excel Discussion (Misc queries) | |||
Macro to hide rows. | Excel Programming | |||
Macro to hide rows | Excel Programming |