VBA Code - Expand / Collapse Grouped Columns

Feb 10, 2014

Currently, Columns EFG are grouped.

I have a userform with checkboxes. With the click of check box, i would like for it to do the following.

If checked, it will expand the group. If unchecked, it needs to stay collapsed.

Here is an example of code i currently have and is not working.

[Code] ......

View 2 Replies


Code To Conditionally Expand Or Collapse Only Certain Grouped Rows

May 13, 2011

I have several groupings of rows in worksheet. I need to have each separate grouping be able to automatically expand or collapse based on a value in each group.

Grouping Rows 11-15
Grouping Rows 21-25
Grouping Rows 31-36

If cell values in A10, A20, or A30 = 1, then the grouping below it would need to be collapsed, and if not, then expanded.

View 3 Replies View Related

Expand/Collapse Columns On Password Protected Worksheets

Apr 30, 2003

Is there a way to password protect a document AND still allow for a user to expand or collapse columns?

Currently, it appear that when a worksheet is password protected, one cannot expand or collapse a column.

View 9 Replies View Related

Trapping Outline Expand / Collapse

May 15, 2009

I suspect I need a class module for this...

How do I trap the event when a user chooses to expand / collapse outlines (columns in this instance) on a worksheet?

Basicallly, when attempting to expand I want to prompt the user to enter a password and then unprotect the sheet. And if collapsing to prompt again and then protect the sheet. I have this bit covered, just not sure how to trap the event.

View 9 Replies View Related

VBA Code To Auto-expand Columns When Pivot Tables Refresh

Mar 30, 2013

I have 2 drop downs that when changed, auto refresh all pivot tables. My problem is getting the columns to auto-adjust based on the refresh or change of the data in the pivot table. Listed below the code I have thus far for the auto-refresh on pivot tables. How to auto expand all columns simultaneously.

The pivot tables are based off of tables on a different sheet(TOS Tables). So the code listed below is in the table sheet, not the pivot table sheet (TOS Customer Level). So followup question will be, which sheet to put the auto-expand columns code?

Private Sub Worksheet_Calculate()
'If data on this worksheet changes, refresh the pivot table
Sheets("TOS Customer Level").PivotTables("PivotTable2").RefreshTable
Sheets("TOS Customer Level").PivotTables("PivotTable5").RefreshTable
End Sub

View 3 Replies View Related

Easy Way To Collapse Rows And Columns On All Sheets In Workbook

Jan 28, 2014

Is there an easy way to collapse rows and columns on all sheets in a workbook.

View 3 Replies View Related

Check If Columns Are Grouped (VBA)

Dec 5, 2008

How can I check (using VBA) if 2 columns (F and G) are already grouped.

View 7 Replies View Related

Calculate Sum Of Grouped Columns

Feb 8, 2008

I have a spreadsheet where columns belonging to the same topic are grouped together. For every group I want to calculate the sum of numbers in a row. How can I get all the columns belonging to a group?

View 8 Replies View Related

Charting Hidden / Grouped Columns?

Mar 18, 2014

I have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...

View 2 Replies View Related

VB To Expand Alphanumeric Code Ranges Into New Column

Jan 8, 2013

I have a column containing ranges of apha-numeric codes that I wish to expand into an adjacent column as the following example:

AL 0-4
AL 0

E 4
AL 1

H 16-23
AL 2

[Code] .....

I would like the VB to distinguish and expand the different range permutations from the first column, leaving single codes intact and separating comma separated codes as shown. If it makes it easier, I manually split out the comma separated codes onto separate rows.

View 4 Replies View Related

Convert One Column Of Addresses Into Three Columns When Text To Be Grouped Is Not Even

Jan 10, 2014

I have checked to see if this has been answered before and can not find an exact reply. My problem is that I have a combined column of address: Suburb, State, Postcode - which needs to be broken into three which not be done accurately using tet to column or fx - left mid or right. The issue is that the Suburb could be made up of one or two Words with a space between. So I can not separate using text to columns as in some cases the suburbs comprised of two words will put the second word in the "state" column. It can also not be done using Left, Right,Mid, as they number of characters differs in each line. example problem with columns to text.jpg


View 12 Replies View Related

VBA Code To Convert Multiple Rows As Column Header Grouped By Unique Key

Jul 24, 2014

I have data in excel sheet in the below format:

Existing view.png

How to write a VBA code or Macro to get it in below format:

Required View.png

Timestamp column is the unique key.

View 1 Replies View Related

Formula To Expand Headings To Table Columns

Jan 25, 2014

The attached workbook has a fragment of a table . The column heads are letters representing job nos. The first row is of numbers which will always be multiples of 1000. Subsequent rows are the factors of each number by each number.

My problem is how to structure the columns of the second table so that if a number has a factor of 1 there is one copy of that column head. if the number has a factor of 2 then there are 2 copies of the head, and so on.

The table can vary in size but the first row of each column will always be an integral number of 1000's (e.g. never 1250).

I have not been able to attach the workbook as "ADDFILES" is not visible in the download screen, I hope the above description will be enough.

View 3 Replies View Related

Excel 2010 :: Lines Count - How To Expand Amount Of Columns In One Spreadsheet

Dec 27, 2011

I'm using Excel 2010. One spreadsheet I'm using has 1048576 lines and is a XLSX file. The other file has 65536 lines and is also a XLSX file. When I try to do a VLOOKUP, I'm given an error stating "invalid reference. This file version cannot contain formulas that reference cells beyond 256 columns or 65536 lines.

If both are XLSX files how do I expand the amount of columns in the one spreadsheet with 65536 lines? I thought all XLSX files had a million lines.

View 4 Replies View Related

Collapse All Levels In Pivot Table

Sep 9, 2010

I created a PivotTable that works fine. A user can click on an option and the subcategories expand; then he can choose one of those and a new group opens, etc., going 4 or 5 levels deep in options.

What I need to do is: If he then wants to start over and choose another option in the initial level, I want all of the subcategories to reset to their original closed state (unexpanded). As it is now, when he goes back to the original choice, that is closed, but everything inside it is still open as he had selected them previously.

View 6 Replies View Related

Double Click To Collapse A Set Number Of Rows

Mar 18, 2014

I am trying to build a worksheet which has an account number in column B with several more rows of content starting in column C and beyond. I would like to be able to hide the 9 rows under the row with the account number by double clicking on the account number. I found this code which will work by collapsing to the next account number, but it collapses the account number underneath it too. Is there a way to collapse only the blank cells in the column until the next number or to program the code to only collapse/hide the next 9 rows below the double clicked cell?

View 2 Replies View Related

Macro To Collapse Subtotals Results In: Cannot Shift Objects Off Sheet

Aug 6, 2008

I'm getting a runtime 1004 error "Cannot Shift Objects Off Sheet" right at the line when i am trying to collapse a subtotal (showlevels, rowlevel 2). I searched about this and i tested. I cannot find any comments, hidden comments, rows or columns. I cannot find any shapes (ran "Kill_Shapes" posted by Aaron Blood).

Sheets("Oxnard Planning 10 (all)").Activate
'SORT: Del Code (D), then Style (A)
Range("A1").Sort Key1:=Range("D1"), Order1:=xlAscending, _
Key2:=Range("A1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
'Subtotal by STYLE
Range("A1").subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(5, 6, 7, 8, 9, 10, 11), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
'Subtotal lines = Bold & Pink
ActiveSheet.Outline.ShowLevels RowLevels:=2 '<< ERROR:Cannot shift Objects off Sheet
With Range(Range("K65536").End(xlUp), "A1").SpecialCells(xlCellTypeVisible)
.Interior.ColorIndex = 38
.Font.Bold = True
End With
ActiveSheet.Outline.ShowLevels RowLevels:=3

View 2 Replies View Related

Use Of Grouped Cells

Aug 31, 2007

I have aprox 700 groups of cells "I selected a set of cells and named them". these groups cover 3500 rows. There sizes very in the number of rows per group but, all have the same number of columns.

What I'm tring to do is create a summary sheet. I want to copy all groups that have meet a single criteria to another sheet. The criteria will always be in the same column. The criteria is part of a function and will change each time I import new data into my excel sheet. Am I even close by trying to group them like this?

View 12 Replies View Related

Can Multiple Tabs Be Grouped Together?

Nov 29, 2009

I have an Excel file with 70+ tabs and was wondering if there was some way to group them together, short of just making separate worksheets.

View 6 Replies View Related

Need To Sort Grouped Data

Nov 29, 2012

I'm working on a spreadsheet to track student test scores over the course of the school year. Each score set is grouped by student, but we need to be able to realphabetize when new students are added. is there some way to accomplish this?

View 3 Replies View Related

Printing Grouped Rows

Aug 11, 2013

I have a few pages of information and have grouped rows collapsed to show only the summary information. When I try to print only visible summary rows, whether I use print, print selection, copy and paste to another sheet or copy and "paste special" values to another sheet, all the hidden/grouped rows print or the summary rows print separately on different pages. Is it possible to print only the summary rows.

View 3 Replies View Related

Closing All Grouped Months?

Jan 23, 2014

I have a spreadsheet with one column for each day. I've grouped the days into months and now I would like to minimize (minimize each month) Is it possible to close all, without having to select each group?

View 1 Replies View Related

Read Grouped Textbox Value

Jan 16, 2008

I have a bunch (between 5-10) textboxes grouped together into one object.

When I click one of them, I want its value to be written to a cell on the active worksheet. In other words, simply would like to read the value of one of the textboxes in the group. This sounds trivial, but at this point, I haven't been able to find the correct syntax to access this property. It seems that the fact that they are grouped hides access to their text? I am using Excel 2003.

I have managed to code a workaround, but its performance is atrocious (involves shaperanges, ungrouping, etc..)

View 9 Replies View Related

Pivot Chart Grouped At Two Levels

Aug 21, 2012

I have daily data that has columns for day, day of week, week end, month, year. I am trying to create a pivot chart that displays the data as a line chart day by day and as a secondary axis column chart by week.

View 1 Replies View Related

Formula Drag Over Grouped Rows

Dec 16, 2013

I have sheets with over 40000 rows. I grouped them in 60 and I need the average for every group on the 61st (which is blank). For example, when collapsed I have visible the following blank cells:

and so on.

What I want is to enter a formula in A61 for the average of A1:A60 then drag down and have the correct values in all the rest (average of A62:A121 in A122, etc.).

View 4 Replies View Related

Data Manipulation & Grouped By The Name Field

Aug 4, 2009


Where all question 1 fields are put into a row, 2 in another row, etc (this may be more than 2 sets of questions), grouped by the name field. I have had a look at Pivot tables but they dont seem to do what Im after.

View 3 Replies View Related

Bargraph With Grouped And Stacked Bars?

Jan 27, 2010

This is the data:

jan09 feb09 mar09 .....
Division 1
Division 2
Division 3
Division 4
Division 5

jan08 feb08 mar08 .....
Division 1
Division 2
Division 3
Division 4
Division 5

The graph would be a bar graph with 2 bars next to each other per month. each bar would be stacked by division. The goal is to compare for example sales in 08 with sales in 09 showing 2 bars next to each other split by division.

View 2 Replies View Related

Sort Data In Grouped Rows

May 22, 2013

I have a workbook (Sort Test.xlsx) where multiple rows belong to the same "group". Is there a way to sort by a value (e.g. name of the test, date of the test, etc.) while maintaining the formatting and keeping the "groups" together? There are 3 sheets in the work book. The first shows the sheet as is, the other two are examples of how I would like to be able to sort the data.

View 2 Replies View Related

Sorting Grouped Dates In Slicer?

Jun 12, 2014

In my pivot table I have "Due Date" with is a column label.

I grouped the dates into weeks by using the "days" grouping and using 7 days.

I created a slicer to allow the user to select with week they want, but the slicer is sorted as though it's a text field and not a date field.


6/1/2015 - 6/7/2015
6/13/2016 - 6/19/2016
6/15/2015 - 6/21/2015
6/16/2014 - 6/22/2014

I want the slicer to sort by actual date.

View 1 Replies View Related

Grouped Labels On Chart X-axis

Feb 20, 2008

I want to create grouped labels on a chart x-axis of the type created automatically when creating a pivot chart. For example: I have projects, samples and sample yields. There are multiple samples in a project so I have a sheet that looks something like this below:-


I would like to create a chart that has Project and Sample as the x-axis where the samples are all grouped into their project. Pivot charts do this nicely, but how to do it without a pivot chart? As you will see from the attached file I can get both Project and Sample on the x-axis, but the Projects are overlapping.

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved