expand/collapse row button

can I place a button any where in the worksheet to collapse/expand rows? I 
would like to place a button +/- on column D to expand/collapse row 2 to 5 
(instead of using the grouping tool where you find the button at the left 
most corner). Thank you for any assistance. 

                A          B          C         D
row 1
row 2
row 3
row 4
row 5
-- 
caii
Excel 2003
0
caii (2)
10/26/2005 7:24:04 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
356 Views

Similar Articles

[PageSpeed] 25

No need for a button

You could put the following code in the Worksheet_Selection Change
event. It will hide rows 2 to 5 whenever a cell in column D is
selected. Similarly it will unhide the rows if column E is selected

If you really want a button, simply use the same code in an attached
macro.

HTH

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, [D:D]) Is Nothing Then
        [2:5].Rows.Hidden = True
    End If

    If Not Intersect(Target, [E:E]) Is Nothing Then
        [2:5].Rows.Hidden = False
    End If

End Sub



caii wrote:
> can I place a button any where in the worksheet to collapse/expand rows? I
> would like to place a button +/- on column D to expand/collapse row 2 to 5
> (instead of using the grouping tool where you find the button at the left
> most corner). Thank you for any assistance.
>
>                 A          B          C         D
> row 1
> row 2
> row 3
> row 4
> row 5
> -- 
> caii
> Excel 2003

0
10/26/2005 8:24:42 AM
Have a look at Filter

You could put (say) and X in column D in the rows that you want hidden and 
use Custom Filter on Columnd D (or on D1:D5) to display only cells that don't 
contain X

Data > Filter > AutoFilter

HTH

Gary

"caii" wrote:

> can I place a button any where in the worksheet to collapse/expand rows? I 
> would like to place a button +/- on column D to expand/collapse row 2 to 5 
> (instead of using the grouping tool where you find the button at the left 
> most corner). Thank you for any assistance. 
> 
>                 A          B          C         D
> row 1
> row 2
> row 3
> row 4
> row 5
> -- 
> caii
> Excel 2003
0
Gary76 (17)
10/26/2005 8:31:01 AM
great and thanks, will try all your suggestions. 
-- 
caii


"Gary76" wrote:

> Have a look at Filter
> 
> You could put (say) and X in column D in the rows that you want hidden and 
> use Custom Filter on Columnd D (or on D1:D5) to display only cells that don't 
> contain X
> 
> Data > Filter > AutoFilter
> 
> HTH
> 
> Gary
> 
> "caii" wrote:
> 
> > can I place a button any where in the worksheet to collapse/expand rows? I 
> > would like to place a button +/- on column D to expand/collapse row 2 to 5 
> > (instead of using the grouping tool where you find the button at the left 
> > most corner). Thank you for any assistance. 
> > 
> >                 A          B          C         D
> > row 1
> > row 2
> > row 3
> > row 4
> > row 5
> > -- 
> > caii
> > Excel 2003
0
caii (2)
10/26/2005 8:44:09 AM
Reply:

Similar Artilces:

Is it normal for a pst file to expand when imported into an ost fi
I had a 2+GB pst file that when imported into my exchange cached environment grew to approximately 20GB. Of course this wreaked havoc on the user's mailbox, and I am now having synchronization and all kind of wierd problems. I guess my first question is, is it normal for a pst file to grow on import? Also, on that import, is it possible for the original pst file to become corrupt, or smaller after the import. I fear that I may have lost some messages in the import In news:88546C4E-0213-4C63-A237-DBF5280576B3@microsoft.com, BookerW <BookerW@discussions.microsoft.com> typ...

Multiple rows of data on a single axis (charting)
I have several rows of data that are separated by several other rows (that I don't want to chart). Is there a way to chart several rows of data on one continuous axis? For example (see below) I want to chart Row 1, Row 3 and Row 5 on one axis that would show a trend of 25, 50, 75, 5, 10, 15, 1, 2, 3. I have tried separting the rows with comas, colons, &, "", + and can't get it to work. I don't want to copy and paste the data into a new format b/c that will take too much time. Any suggestions? Column A Column B Column C Row 1 25 ...

Enable Command Button base on UserName
I have a Command Button on a worksheet that I only want to display if a specified User Opens the workbook. I have the following code: (in a UserName module) ================= ' By Chris Rae, 14/6/99, 3/9/00. Option Explicit ' This is used by GetUserName() to find the current user's ' name from the API Declare Function Get_User_Name Lib "advapi32.dll" Alias _ "GetUserNameA" (ByVal lpBuffer As String, _ nSize As Long) As Long Function GetUserName() As String Dim lpBuff As String * 25 Dim txtName As String Get_U...

how can I find two same rows in a column?
I've got a file where I put all my invoices numbers. It's a long file, so how can I know if a put the same invoice number twice??? Thanks a lot!!! Hi see: http://cpearson.com/excel/duplicat.htm#TaggingDuplicates "Anevigat" wrote: > I've got a file where I put all my invoices numbers. It's a long file, so how > can I know if a put the same invoice number twice??? > Thanks a lot!!! ...

Pulling Specific Rows from a Table onto a New Sheet
I have a table consisting of multiple rows and columns on a sheet and I need to break out certain rows depending on a specific value in the column into a new sheet. For example, I have a list with multiple "companies", "contract numbers", "contract dates", "contract amounts", "payment dates", etc... I need to break out all the rows of a specific company into another sheet. On that sheet I need to break it out even further by "contract number" to show what has been paid and what has not (which is in the original table). I would try t...

Clear all rows except first two
Hi, I am using Excel 2007. I want to write a VBA Subroutine to delete/clear all data on a particular worksheet except for the first two rows. This worksheet can have any number of rows filled with data. Can anyone give me a hint on how I can select and clear all rows containing data except for the first two rows? Thanks in advance, Paul -- Paul Kraemer sub clearrows() dim lr as long lr = Cells.Find("*", Cells(Rows.Count, Columns.Count) _ , , , xlByRows, xlPrevious).Row rows(3).resize(lr).delete end sub -- Don Gu...

Expanding Folders #2
I have a Hotmail account that I read with Outlook 2003, but I can't figure out how to keep the "Hotmail" folder expanded so I can see the "inbox", "Deleted items," "junk email" and "sent items" sub-folders. Thanks in advance for your assistance tubbfan <tubbfan@excite.com> wrote: > I have a Hotmail account that I read with Outlook 2003, but I can't > figure out how to keep the "Hotmail" folder expanded so I can see the > "inbox", "Deleted items," "junk email" and "sent items...

duplicate the each row twice in one worksheet
I have 89 rows data (first name, last name) in one worksheet, for some reason I need to repeat their name 3 times for some reasons. Is there any easy way to do so wihtout running copy 3 times of 89 rows. Any tip would be much appreciated. Regards, MH Select the whole lot, copy and then paste at the bottom of your data. Repeat then sort on name. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------�------------------------------�---------------- It's easier to beg forgiveness tha...

Collapse a report
I just finished a custom report, but I want it to default to the collapsed view. Is there a way to do that? after you generate the report, collapse it and the memorize it. the next time you generate that memorized report it will open collapsed. Craig "Rick@ASP" <RickASP@discussions.microsoft.com> wrote in message news:E20535CF-AE5E-4C60-90CE-9802FAA2FBAB@microsoft.com... >I just finished a custom report, but I want it to default to the collapsed > view. Is there a way to do that? It didn't work. The memorized one looks just like the original. ...

Maximum Rows and Columns
Just got my new Office 2007 Suite of applications. One reason I wanted the new Office is that I work with large data files and the capability of 1 million rows and a lot of columns will help me. Unfortunately, I just tried to combine 2 large worksheets that totalled more than 66,000 rows and got the error message that I had exceeded the row maximum. How do I "turn on" the larger row and column capability? Any help would be much appreciated. Thanks. -- PBear You probably opened older format Excel files. Office button>Save as... choose Excel workbook (or macro enables w...

turn columns into rows
I have a large spreadsheet that I'd like to have in two separate formats: One, with Data X in Columns and Data Y in Rows, the other with Data Y in Columns and Data X in Rows. How can I do this while preserving the data? Select data, right click, choose "Copy". Right click in first cell of new location, choose "Paste Special", and click on "Transpose", then <OK>. Don't forget ... you only have 256 columns. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==========...

Report Button appears then disappears then reappears
All of my users at one time or another have access to the Reports area. Recently I have had a report button that is there, then disappears, then randomly reappears in all of my Outlook Laptop client users only. I don't have this trouble with my users who are on the Outlook Desktop client. I have found no way to "jiggle the cables" to make the report button or access reappear. For my users who have this problem, which isn't all of my users, and it is random, even going to the web client, they still cannot get access to reports. I've tried repairing the clien...

Help with Autofit Row Height Macros
Hello! I need to have Excel 2003 automatically adjust row height for protected cells with text. Here are the two codes I have. Which is better, Worksheet Calculate or Selection Change? Also, what is the difference between Range("A28:32) and Rows ("28:32")? Basically, is one of these codes better than the other? s there anything you would recommend to increase their reliability? Thanks! VR/Lost Private Sub Worksheet_Calculate() Application.EnableEvents = False ActiveSheet.Unprotect "password" Range("a28:32").EntireRow.AutoFit A...

CRM 4.0 Distribute Campaign Activity Button not showing
When we go into a Marketing Campaign with a Marketing list of Contacts and create a Campaign Activity be it a Phone Call, Email, Email Merge etc and save it the CRM 4.0 Distribute Campaign Activity Button does not display / is not avalible to click to distribute. We have tried it with the user having the CRM System Administrator Security role but no change. Any ideas? Thanks The Campaign had been created as a template campaign. Once it was copied to a Campaign the distribute button was avaliable. "B" wrote: > When we go into a Marketing Campaign with a Marketing list o...

Expand Tag Along
hi, how can i expand the tag along item ,that the cashier can select the tagged item from a list of items (choose a gift for customer sold free with an item). thanks ...

Inserting a Row in Exel 2003
I've just started using Excel 2003 after a long, happy use of 97. Whenever I insert a row into a sheet, I get a little paintbrush Icon which apparently will format the row according to the format of the row above or below it. How do I get rid of this so it doesn't appear each time I insert a row? I do that a lot. Thank you very much. Tools/Options/Edit/... Take off the check for Show Intesert Option Buttons. "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message news:10jbsrcpkhp9qf9@corp.supernews.com... > I've just started using Excel 2003 after a l...

Printing Frozen Rows
I have a workbook with 2 worksheets in it - Sheet A and Sheet B. Both sheets are 3 pages long as far as printing goes. On both sheets (A & B) I have frozen the top 2 rows so I can view the column headings no matter where I have scrolled down to on the page. Sheet A will print these top two rows on all sheets that it prints (all 3 pages in this example) but sheet B will only print them on the first page. Does anyone know why? I would like to have sheet B print them on all pages too. Thanks in advance for any help. Check under File|Page setup|Sheet tab I'm guessing you don&...

Multi-level Category Axis Fails with >100 rows of data #3
When working with multi-level category axes Excel 2007 incorrectly displays the axis when there are more than 100 rows of data included in the chart. Many of the labels are not displayed. "V" provided a couple of examples - a "correct" multilevel axis and 100 rows of data: http://tinypic.com/view.php?pic=210nint&s=5 - an incorrect chart is one with one extra row (101 total): http://tinypic.com/view.php?pic=2u721eg&s=5 Any ideas as to how to get around this in Excel 2007 would be appreciated, as I use these multi level category axes a lot. (Thanks to "V&q...

Delete specified critria rows
Hi What code can I use to delete rows based on the values of two fields/columns on the same row. (if cell A1 is not blank and cell B1=0 delete) Thanks. Insert a row at the top, and then a spare column, add =AND(A2<>"",B2=0) Select this column and Menu Data>AuItofilter. Click the dropdown and select TRUE from the list Delete all visible rows, including row 1 All done -- HTH RP (remove nothere from the email address if mailing direct) "rn" <anonymous@discussions.microsoft.com> wrote in message news:1f0101c52e0f$23c30e20$a401280a@phx.gbl... > H...

Format excel sheet where one row is silver and the next white ???
I have an excel sheet where it is formatted that one row has a silver background and the next has a white background .... How is it done ???? Hi see: http://www.cpearson.com/excel/banding.htm -- Regards Frank Kabel Frankfurt, Germany "nach" <nach@discussions.microsoft.com> schrieb im Newsbeitrag news:DFB79B00-840B-4234-BD74-0A9C4B2763D4@microsoft.com... >I have an excel sheet where it is formatted that one row has a silver > background > and the next has a white background .... > > How is it done ???? nach wrote: > I have an excel sheet where it is ...

Row Colors
In Excel 2007, need to differentiate rows by color. In column M, "H" = Color 1 "A" = Color 2 "P" = Color 3 "L" = Color 4 I will be printing data from the spreadsheet, so I'd like to use lighter shades of colors rather than the bright, primary colors that will show up dark on printed pages. Thanking you in advance. Betty Betty, i don't understand when you say "H", "A"....etc do you mean if the letter is found in column M? Betty K;578287 Wrote: > In Excel 2007, need to differentiate rows by colo...

Oulook, Expand/Collapse How to setup from Expand all the time.
It drive me nut to reset this each time I need to look at my contact. Need help on this on. Ernie You can set that in the view settings. While in the view in question go to the View menu > Current View > Customize Current View > Group By dialog and set the expand/collapse setting at the bottom "Ernie" <Ernie@discussions.microsoft.com> wrote in message news:AF7CD991-6AA9-47A8-8B5A-4F601176399C@microsoft.com... > It drive me nut to reset this each time I need to look at my contact. > Need help on this on. > Ernie ...

highlighting rows is there an macro for this
I have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr cell on any row is it possible to high light that whole row if there is a way please tell me how!!! See some choices in your other post in Misc. Regards, Howard "Mike" <Mike@discussions.microsoft.com> wrote in message news:400C7CBD-7A47-4C28-85A7-C77AA50106F5@microsoft.com... > I have columns from A1 to A300 TO CP1 TO CP300 if I click on a particulr > cell > on any row is it possible to high light that whole row if there is a way > please tell me how!!! ...

count number of rows in 2 worksheets
HI, Anyone can help? I need a macro code to count number of used rows in two seperate worksheets and compare. If the number do not match error message shall appear "Sheet1 has (blank) number and Sheet2 has (blank) number". (blank) being the number of used rows per sheet. Can this be done? thanks! You have some answers in your first posting of this question. However, I'd like to tell you that your question is not defined very well. You have to tell us what you mean by "used rows". For example, if there are blank rows inside your data, are they to be coun...

Automatically Expanding the GL Transaction Entry Grid
Does anybody know the code to automatically expand the grid on the GL Transaction Entry window? I tried to use the same code (adapted) that I used on the payables transaction distribution window, namely: scrollexpandswitch.value = 1 But I got an error when it ran. -- Charles Allen, MVP Using VBA, that is. -- Charles Allen, MVP "Charles Allen" wrote: > Does anybody know the code to automatically expand the grid on the GL > Transaction Entry window? I tried to use the same code (adapted) that I used > on the payables transaction distribution window, namely: >...