Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
I need to use a single cell to show/hide other rows. For instance if you put
a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
Not sure exactly what you want, but try this.
Sub HideUnhide() Rows("2:" & Rows.Count).Hidden = True If Range("B1") = 6 Then Rows("2:13").Hidden = False ElseIf Range("B1") = 10 Then Rows("2:21").Hidden = False End If End Sub "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
That is close. What I need is that you can choose from 1 to 35 (maybe in a
pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
I don't completely understand your criteria. From your example, if the user
picks 2, how do you know to start showing rows at Row 3? And if you hide everything except for Rows 3 through 6, then B1 will be hidden and you won't be able to change the number in it again without manually unhiding it. Can you give us more details on how you want this functionality to work (keep in mind that no one here has any idea what you want to do, so you have to tell us). Rick "New2Macros" wrote in message ... That is close. What I need is that you can choose from 1 to 35 (maybe in a pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
New2Macros:
Sorry I'm not being clear enough. Say in cell A1 you want to enter the number of rows being shown in the spreadsheet. A1 should always be shown, of course, and each number entered in A1 will show double the amount of rows below. Each row starting with A2 will have information entered previously, so we are just displaying, or hiding those rows below. For instance: if I want only 2 rows displaying below, I would enter a "1" in cell A1. If I want 10 rows to show, I would enter "5" in A1, and so on. A1 probably will have a pull down. "Rick Rothstein (MVP - VB)" wrote: I don't completely understand your criteria. From your example, if the user picks 2, how do you know to start showing rows at Row 3? And if you hide everything except for Rows 3 through 6, then B1 will be hidden and you won't be able to change the number in it again without manually unhiding it. Can you give us more details on how you want this functionality to work (keep in mind that no one here has any idea what you want to do, so you have to tell us). Rick "New2Macros" wrote in message ... That is close. What I need is that you can choose from 1 to 35 (maybe in a pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
Okay, the 'start at Row 3' was a mistype then. By the way, you originally
said B1 would have the number in it, but now are saying A1. No problem, I'll use A1 in my example. Give this a try and see if it does what you want. Right-click the tab on the worksheet where you want to have this functionality and select View Code from the popup menu that appears. This will take you into the VBA editor and present an already opened code window to you. Copy/Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(2 + 2 * Range("B1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub Now, go back to the worksheet and type different numbers into A1 to see if the code is doing what you want. You can put a drop-down list into A1 if you want... it will not matter to the code how the number gets into the cell. Rick "New2Macros" wrote in message ... New2Macros: Sorry I'm not being clear enough. Say in cell A1 you want to enter the number of rows being shown in the spreadsheet. A1 should always be shown, of course, and each number entered in A1 will show double the amount of rows below. Each row starting with A2 will have information entered previously, so we are just displaying, or hiding those rows below. For instance: if I want only 2 rows displaying below, I would enter a "1" in cell A1. If I want 10 rows to show, I would enter "5" in A1, and so on. A1 probably will have a pull down. "Rick Rothstein (MVP - VB)" wrote: I don't completely understand your criteria. From your example, if the user picks 2, how do you know to start showing rows at Row 3? And if you hide everything except for Rows 3 through 6, then B1 will be hidden and you won't be able to change the number in it again without manually unhiding it. Can you give us more details on how you want this functionality to work (keep in mind that no one here has any idea what you want to do, so you have to tell us). Rick "New2Macros" wrote in message ... That is close. What I need is that you can choose from 1 to 35 (maybe in a pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
This seemed to work right away, but now I'm getting an "argument not
optional" message when I put a number. What could this mean? "Rick Rothstein (MVP - VB)" wrote: Okay, the 'start at Row 3' was a mistype then. By the way, you originally said B1 would have the number in it, but now are saying A1. No problem, I'll use A1 in my example. Give this a try and see if it does what you want. Right-click the tab on the worksheet where you want to have this functionality and select View Code from the popup menu that appears. This will take you into the VBA editor and present an already opened code window to you. Copy/Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(2 + 2 * Range("B1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub Now, go back to the worksheet and type different numbers into A1 to see if the code is doing what you want. You can put a drop-down list into A1 if you want... it will not matter to the code how the number gets into the cell. Rick "New2Macros" wrote in message ... New2Macros: Sorry I'm not being clear enough. Say in cell A1 you want to enter the number of rows being shown in the spreadsheet. A1 should always be shown, of course, and each number entered in A1 will show double the amount of rows below. Each row starting with A2 will have information entered previously, so we are just displaying, or hiding those rows below. For instance: if I want only 2 rows displaying below, I would enter a "1" in cell A1. If I want 10 rows to show, I would enter "5" in A1, and so on. A1 probably will have a pull down. "Rick Rothstein (MVP - VB)" wrote: I don't completely understand your criteria. From your example, if the user picks 2, how do you know to start showing rows at Row 3? And if you hide everything except for Rows 3 through 6, then B1 will be hidden and you won't be able to change the number in it again without manually unhiding it. Can you give us more details on how you want this functionality to work (keep in mind that no one here has any idea what you want to do, so you have to tell us). Rick "New2Macros" wrote in message ... That is close. What I need is that you can choose from 1 to 35 (maybe in a pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
I'm not sure why you are getting that message. Did you modify the code in
any way? Is it highlighting a line of code when it displays the message? If so, which line. If not, put a breakpoint on the first line of my code (click the left hand border and a red dot will appear along with the line being highlighted in red indicating the breakpoint is set); then change the value in B1. You will be taken back to the code window and the line with the breakpoint will be highlighted in yellow. Pressing F8 will execute the line of code and advance the yellow highlight to the next executable line. Keep pressing F8 until the message appears. Which line was the last one highlighted in yellow before the error appeared? Rick "New2Macros" wrote in message ... This seemed to work right away, but now I'm getting an "argument not optional" message when I put a number. What could this mean? "Rick Rothstein (MVP - VB)" wrote: Okay, the 'start at Row 3' was a mistype then. By the way, you originally said B1 would have the number in it, but now are saying A1. No problem, I'll use A1 in my example. Give this a try and see if it does what you want. Right-click the tab on the worksheet where you want to have this functionality and select View Code from the popup menu that appears. This will take you into the VBA editor and present an already opened code window to you. Copy/Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(2 + 2 * Range("B1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub Now, go back to the worksheet and type different numbers into A1 to see if the code is doing what you want. You can put a drop-down list into A1 if you want... it will not matter to the code how the number gets into the cell. Rick "New2Macros" wrote in message ... New2Macros: Sorry I'm not being clear enough. Say in cell A1 you want to enter the number of rows being shown in the spreadsheet. A1 should always be shown, of course, and each number entered in A1 will show double the amount of rows below. Each row starting with A2 will have information entered previously, so we are just displaying, or hiding those rows below. For instance: if I want only 2 rows displaying below, I would enter a "1" in cell A1. If I want 10 rows to show, I would enter "5" in A1, and so on. A1 probably will have a pull down. "Rick Rothstein (MVP - VB)" wrote: I don't completely understand your criteria. From your example, if the user picks 2, how do you know to start showing rows at Row 3? And if you hide everything except for Rows 3 through 6, then B1 will be hidden and you won't be able to change the number in it again without manually unhiding it. Can you give us more details on how you want this functionality to work (keep in mind that no one here has any idea what you want to do, so you have to tell us). Rick "New2Macros" wrote in message ... That is close. What I need is that you can choose from 1 to 35 (maybe in a pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
I don't get it - no matter where I put a breakpoint, nothing changes. I get
nothing highlighted. "Rick Rothstein (MVP - VB)" wrote: I'm not sure why you are getting that message. Did you modify the code in any way? Is it highlighting a line of code when it displays the message? If so, which line. If not, put a breakpoint on the first line of my code (click the left hand border and a red dot will appear along with the line being highlighted in red indicating the breakpoint is set); then change the value in B1. You will be taken back to the code window and the line with the breakpoint will be highlighted in yellow. Pressing F8 will execute the line of code and advance the yellow highlight to the next executable line. Keep pressing F8 until the message appears. Which line was the last one highlighted in yellow before the error appeared? Rick "New2Macros" wrote in message ... This seemed to work right away, but now I'm getting an "argument not optional" message when I put a number. What could this mean? "Rick Rothstein (MVP - VB)" wrote: Okay, the 'start at Row 3' was a mistype then. By the way, you originally said B1 would have the number in it, but now are saying A1. No problem, I'll use A1 in my example. Give this a try and see if it does what you want. Right-click the tab on the worksheet where you want to have this functionality and select View Code from the popup menu that appears. This will take you into the VBA editor and present an already opened code window to you. Copy/Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(2 + 2 * Range("B1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub Now, go back to the worksheet and type different numbers into A1 to see if the code is doing what you want. You can put a drop-down list into A1 if you want... it will not matter to the code how the number gets into the cell. Rick "New2Macros" wrote in message ... New2Macros: Sorry I'm not being clear enough. Say in cell A1 you want to enter the number of rows being shown in the spreadsheet. A1 should always be shown, of course, and each number entered in A1 will show double the amount of rows below. Each row starting with A2 will have information entered previously, so we are just displaying, or hiding those rows below. For instance: if I want only 2 rows displaying below, I would enter a "1" in cell A1. If I want 10 rows to show, I would enter "5" in A1, and so on. A1 probably will have a pull down. "Rick Rothstein (MVP - VB)" wrote: I don't completely understand your criteria. From your example, if the user picks 2, how do you know to start showing rows at Row 3? And if you hide everything except for Rows 3 through 6, then B1 will be hidden and you won't be able to change the number in it again without manually unhiding it. Can you give us more details on how you want this functionality to work (keep in mind that no one here has any idea what you want to do, so you have to tell us). Rick "New2Macros" wrote in message ... That is close. What I need is that you can choose from 1 to 35 (maybe in a pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
I'm not sure what to tell you as I can't duplicate the problem here. Just in
case something accidentally changed that you are not aware of, delete the existing Worksheet_Change event code and then copy/paste (don't type) the code I posted earlier into that code window. Did doing that fix the problem? If not, send your worksheet to me (remove the obvious stuff from my email address) and I'll see if it does the same thing you are seeing here on my system. I'll answer back to this thread for continuity sake. Rick "New2Macros" wrote in message ... I don't get it - no matter where I put a breakpoint, nothing changes. I get nothing highlighted. "Rick Rothstein (MVP - VB)" wrote: I'm not sure why you are getting that message. Did you modify the code in any way? Is it highlighting a line of code when it displays the message? If so, which line. If not, put a breakpoint on the first line of my code (click the left hand border and a red dot will appear along with the line being highlighted in red indicating the breakpoint is set); then change the value in B1. You will be taken back to the code window and the line with the breakpoint will be highlighted in yellow. Pressing F8 will execute the line of code and advance the yellow highlight to the next executable line. Keep pressing F8 until the message appears. Which line was the last one highlighted in yellow before the error appeared? Rick "New2Macros" wrote in message ... This seemed to work right away, but now I'm getting an "argument not optional" message when I put a number. What could this mean? "Rick Rothstein (MVP - VB)" wrote: Okay, the 'start at Row 3' was a mistype then. By the way, you originally said B1 would have the number in it, but now are saying A1. No problem, I'll use A1 in my example. Give this a try and see if it does what you want. Right-click the tab on the worksheet where you want to have this functionality and select View Code from the popup menu that appears. This will take you into the VBA editor and present an already opened code window to you. Copy/Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(2 + 2 * Range("B1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub Now, go back to the worksheet and type different numbers into A1 to see if the code is doing what you want. You can put a drop-down list into A1 if you want... it will not matter to the code how the number gets into the cell. Rick "New2Macros" wrote in message ... New2Macros: Sorry I'm not being clear enough. Say in cell A1 you want to enter the number of rows being shown in the spreadsheet. A1 should always be shown, of course, and each number entered in A1 will show double the amount of rows below. Each row starting with A2 will have information entered previously, so we are just displaying, or hiding those rows below. For instance: if I want only 2 rows displaying below, I would enter a "1" in cell A1. If I want 10 rows to show, I would enter "5" in A1, and so on. A1 probably will have a pull down. "Rick Rothstein (MVP - VB)" wrote: I don't completely understand your criteria. From your example, if the user picks 2, how do you know to start showing rows at Row 3? And if you hide everything except for Rows 3 through 6, then B1 will be hidden and you won't be able to change the number in it again without manually unhiding it. Can you give us more details on how you want this functionality to work (keep in mind that no one here has any idea what you want to do, so you have to tell us). Rick "New2Macros" wrote in message ... That is close. What I need is that you can choose from 1 to 35 (maybe in a pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to show/hide rows
I got this macro to work on typical rows, but the odd thing is that if a row
contains a combo box (which references a different sheet), it will give a run-time error. It says "unable to set the hidden property of the range class." I think this is what has been causing the problem from the start. Any ideas? "Rick Rothstein (MVP - VB)" wrote: I'm not sure what to tell you as I can't duplicate the problem here. Just in case something accidentally changed that you are not aware of, delete the existing Worksheet_Change event code and then copy/paste (don't type) the code I posted earlier into that code window. Did doing that fix the problem? If not, send your worksheet to me (remove the obvious stuff from my email address) and I'll see if it does the same thing you are seeing here on my system. I'll answer back to this thread for continuity sake. Rick "New2Macros" wrote in message ... I don't get it - no matter where I put a breakpoint, nothing changes. I get nothing highlighted. "Rick Rothstein (MVP - VB)" wrote: I'm not sure why you are getting that message. Did you modify the code in any way? Is it highlighting a line of code when it displays the message? If so, which line. If not, put a breakpoint on the first line of my code (click the left hand border and a red dot will appear along with the line being highlighted in red indicating the breakpoint is set); then change the value in B1. You will be taken back to the code window and the line with the breakpoint will be highlighted in yellow. Pressing F8 will execute the line of code and advance the yellow highlight to the next executable line. Keep pressing F8 until the message appears. Which line was the last one highlighted in yellow before the error appeared? Rick "New2Macros" wrote in message ... This seemed to work right away, but now I'm getting an "argument not optional" message when I put a number. What could this mean? "Rick Rothstein (MVP - VB)" wrote: Okay, the 'start at Row 3' was a mistype then. By the way, you originally said B1 would have the number in it, but now are saying A1. No problem, I'll use A1 in my example. Give this a try and see if it does what you want. Right-click the tab on the worksheet where you want to have this functionality and select View Code from the popup menu that appears. This will take you into the VBA editor and present an already opened code window to you. Copy/Paste the following into that code window... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("2:" & Rows.Count).EntireRow.Hidden = False Range(CStr(2 + 2 * Range("B1").Value) & ":" & _ Rows.Count).EntireRow.Hidden = True End If End Sub Now, go back to the worksheet and type different numbers into A1 to see if the code is doing what you want. You can put a drop-down list into A1 if you want... it will not matter to the code how the number gets into the cell. Rick "New2Macros" wrote in message ... New2Macros: Sorry I'm not being clear enough. Say in cell A1 you want to enter the number of rows being shown in the spreadsheet. A1 should always be shown, of course, and each number entered in A1 will show double the amount of rows below. Each row starting with A2 will have information entered previously, so we are just displaying, or hiding those rows below. For instance: if I want only 2 rows displaying below, I would enter a "1" in cell A1. If I want 10 rows to show, I would enter "5" in A1, and so on. A1 probably will have a pull down. "Rick Rothstein (MVP - VB)" wrote: I don't completely understand your criteria. From your example, if the user picks 2, how do you know to start showing rows at Row 3? And if you hide everything except for Rows 3 through 6, then B1 will be hidden and you won't be able to change the number in it again without manually unhiding it. Can you give us more details on how you want this functionality to work (keep in mind that no one here has any idea what you want to do, so you have to tell us). Rick "New2Macros" wrote in message ... That is close. What I need is that you can choose from 1 to 35 (maybe in a pull down), and have it show the twice that many rows. So if you pick 2 it would show rows 3 through 6 and hide all the other pre-populated rows. Also when I plugged in that macro I'm getting an error '1004'. "Unable to set the hidden property of the range class." "New2Macros" wrote: I need to use a single cell to show/hide other rows. For instance if you put a "6" in cell B1 it would show the next 12 rows and hide the rest. If you put in a "10" it would show the next 20 rows and hide the rest, etc. I'm new to writing macros, sorry! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple macro to hide and show detail - Grouped Rows | Excel Programming | |||
Macro to hide/show rows and columns | Excel Discussion (Misc queries) | |||
Hide Rows - copy and paste only rows that show | Excel Worksheet Functions | |||
Hide and show Rows | Excel Discussion (Misc queries) | |||
macro prompt to show/hide rows | Excel Programming |