Totally Ungroup Existing Grouping Of Rows In Sheet?

Feb 3, 2012

I need to totally ungroup existing grouping of rows in a sheet. Totally ungroup = strip it totally of any grouping. In short, it should be back to its original state of no grouping at all.

Problem is that I do not know if the sheet has existing grouping, or if it does, how many levels of grouping.

The solution I have in mind right now is just to indiscriminately run ungrouping vba line 10X and just place an error handler i.e.

Sub Macro1()
On Error Resume Next


Is there a shorter way to handle this code-wise?

View 3 Replies


Macro Runs When Change Cells In Totally Different Sheet?

Jun 18, 2014

So I have a worksheet that has a lot of macros in it. I have just barely run into a problem that happens anytime a change a cell in another workbook that I have open. The second workbook I have open isn't even a macro enabled workbook. But for some reason it gives me an error and says I am trying to run this code.

[Code] ....

It gives me the runtime error 9. This code is supposed to run when I click on a combobox in my main workbook but it is running anytime I change something in my other simple workbook. Why this happens and how to stop it? I have had a lot of similar problems in the past but I just dealt with it by only having one workbook open but in this case I will need both.

View 2 Replies View Related

Delete Rows Of Data From Existing Sheet

Dec 26, 2011

I have an excel list of My Existing Customers and have recently purchased an excel list of all possible customers in my market that happens to have my existing customers listed in it as well. How can I remove my existing customers out of this purchased list so that I can import it into my Database as Prospects? I'm using Excel 2010. Deleting duplicates doesnt work for this. I want a function that looks at data in one spreadsheetA and if it finds it in the second spreadsheetB, it deletes the row out of SpreadsheetB.

View 9 Replies View Related

VBA - Ungroup All Rows Using Macro?

Mar 9, 2014

I've Sheet with Group off rows i want to ungroup All rows and do some calculation then

View 1 Replies View Related

Macro To Apply Existing Formula To All Rows In A Sheet

Jan 26, 2009

I'm currently applying these formulae manually, and also instructing other people to do the same, due to my lack of understanding of macros. Hopefully someone can give me some instruction as to how to do this automatically. I'd like people to be able to open sheets sent as normal CSV's and apply a macro to get them into the right format, changing only rows with data in them.

These are the instructions I'm currently sending:

In cell n1 enter:
full phone number

in cell m2 enter:
=RIGHT("00000000" & J2,8)

Drag m2 to last record

In cell n2 enter:

Drag n2 to last record

In cell o1 enter:
Date of call

in cell o2 enter:

View 6 Replies View Related

Ungroup Rows On Cell Click

Jan 30, 2007

Is it possible to ungroup all rows using a cell click instead of the default option that excel gives?

I know this seems a bit pointless but i submit my excel spreadsheets to many people that do not notice the plus & minus signs that excel creates & misses the hidden data.

This way would allow me to put a simple Open / Close text in a cell that would be obvious to click on.

View 4 Replies View Related

Group / Ungroup Within Protected Sheet?

Jun 12, 2013

I have put the following code into my excel workbook to allow me to ungroup sections in a protected sheet. How can I change it to specify multiple worksheets?

Private Sub Workbook_Open()
With Worksheets("P5")
.EnableOutlining = True
.Protect Password:="rob", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

View 4 Replies View Related

Allow For Group/ungroup On Protected Sheet

Jun 1, 2006

I'm building a template for various users. I have protected the sheet with only certain cells available for input. The file is very large and there are some sections that can be group/ungrouped as the user desires.

however, once I protect the sheet, they can no longer click the - or + to expand and collapse. How can I enable group/ungroup on a protected sheet?

View 9 Replies View Related

Protect Sheet With Freedom Of Group / Ungroup

Jun 1, 2014

Once I "protect sheet" , i can not group / ungroup the column / row. after protecting sheet, I want the freedom to group/ungroup the column.

View 2 Replies View Related

Excel 2007 :: Macro - Match Data And Copy Rows To Existing Sheet

Jan 11, 2012

I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.

I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:

Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.

I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.

I'm running Excel 2007.

View 5 Replies View Related

Create A Scoreboard - VLookup Are Totally Blank When Try To Rank It?

Apr 9, 2014

I am trying to create a scoreboard or leaderboard for my job. I am using VLookup to pull information from another worksheet and that working fine. And when I get that information I am ranking it. Here is my problem... Some of the data cells that are being pulled using VLookup are blank totally blank so when I try to rank it, it say #Value!. Basically I want that blank cell to be a zero instead so that I can rank it 0 being the lowest score 3in the ranking.

View 3 Replies View Related

VBA Grouping Selected Rows

Feb 4, 2005

I have VBA code which selects a group of rows ranging between 1 row and 25 rows. I then want code to group the selected rows, however, the only code I know for grouping requires that I enter a specific starting and ending row (ExecuteExcel4Macro "SHOW.DETAIL(1,#,FALSE,#)").

Is there a way to have VBA group and hide a number or rows which is not known in advance and will change between daily executions of the code?

View 3 Replies View Related

Grouping Of Rows Dynamically

Oct 21, 2009

I am having a problem with a VBA macro I am trying to write. All I want to do is check all rows in a worksheet and group rows from the last bold row to the next bold row.

I seem to have the conditional logic correct, however, instead of grouping rows separately in each loop, it keeps joining them together.

View 6 Replies View Related

Minimum Value In Grouping Of Rows

Apr 26, 2012

I have a grouping and subgrouping of rows in my worksheet and I want to use the minimum value from each group and subgroup and use those values in a report on a separate worksheet using vba. Here's the situation

Emp List (Column)__________________________ Salary
- Workers
__+ Foremen
__- Juniors
- Management
__+ Seniors
__+ Middle

Lets say Max has the lowest salary among all the workers then I want the report to look like this

Emp Group__________ Emp____________________ Min Salary
Workers____________ Max____________________ 1800

Can I do this using vba without multiple loops at all the group levels?

View 7 Replies View Related

Grouping Rows Based On Value Of 9 Cells?

Jun 5, 2012

I have a permutation with repeatition of 3 letters in 9 digits 3^9, and I'd like to be able to group all posibble permutations something like:

BBBBBB+2+1 = BBBBBBCCD = 252 rows
BBBB +3+2 = BBBBCCCDD = 1260 rows
5+3+1 and so on

View 2 Replies View Related

2-level Dynamic Grouping Of Rows

Jul 8, 2008

I have a spreadsheet that requires grouping at two levels. See pasted image below.

I need a macro that on the first pass dynamically creates groups of different sizes of data based on values of one column. For example, a group is created for the RR's, a group is created for the SS's, and a group is created for TT's.

On the second pass I would like to group the groups based on the values of another column for the 2nd level of grouping. So the A's would be one group and the B's would be the other.


I can have 2 tables set up for the values that I need grouped at each level.

Table 1Table 2Group 1Group 2ARRBSSCTTUVWXY

I had recorded a macro where I manually grouped the data. However, this did not help, because I am creating a template that needs to dynamcally handle the data that comes in.

View 9 Replies View Related

VBA For Automatically Grouping Rows Based On Text

Jul 7, 2009

I have a pivot table (vendor spend report) which has over 7000 rows of data. There are 38 vendors (some vendors appear multiple times i.e. Accenture Itay, Accenture Germany, etc.). Vendors names are in column B.

I need to have the VBA script search for each of the vendors (and related names) and group them together. Also, last one is for all the MISC. vendors which need to be group together (one's that dont belong to the 38 managed vendors).

Also, I need to rename the Group for each one; rather than Group1, I need to name it "Accenture All" etc...

View 7 Replies View Related

Pagebreaks: Grouping Rows Together So Print On Same Page

Feb 27, 2007

I have a report that shows 3 lines for each item. Is there a way to group sets of 3 rows together so they print on the same page so that it would insert a page break either before or after each set of 3 rows, not between them.

View 9 Replies View Related

How To Create Duplicate Rows Of All The Existing Rows

Jun 26, 2013

Everyone always want to get rid of duplicate rows.

Is it possible to create duplicate rows of all the existing rows?

View 5 Replies View Related

Grouping Objects Then Copying And Pasting Onto Another Sheet

Dec 18, 2008

doesnt work when recording a macro because the group name changes each time I group the pictures (visios) and the picture names change depending on what is on the sheet at the time.

Is there a way to group every picture on a sheet1 (excluding macro buttons), then copy them to sheet 2?

View 14 Replies View Related

Open New Sheet With Data Off Of Existing Sheet?

Feb 5, 2012

I am trying to prepare a Worksheet that has an initial sheet which is a summary of all sheets, information on individuals and totals etc. This summary sheet will be added to over time. I would like it so that when I enter the details of a new individual on the summary sheet a new sheet is opened following a template with the details already filled in for that individual, and for the new sheet to be named with the individual's name. Ideally the new sheet would open automatically once the info has been entered, either that or a button to open the new sheet,

View 3 Replies View Related

Macro Inserts 3 Rows Below Each Existing Row Of Data And Copies And Pastes That Data Into Each Of The Empty Rows

Nov 30, 2009

need to create a macro that inserts 3 rows below each existing row of data and simply copies and pastes that data into each of the empty rows before moving on to the next unique row and doing the same thing again.

This is what I have so far, but I can't seem to get the loop right.

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

activecell.Offset(-1, 0).Select
Range(activecell, activecell.Offset(0, 5)).Copy
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
activecell.Offset(1, 0).PasteSpecial
Selection.Offset(1, 0).EntireRow.Select

View 5 Replies View Related

Adding Two Rows After Every Existing Row

Aug 7, 2009

Is there any way to automatically add two new rows after every existing row containing data?

For example

1 A B C
2 D E F
3 G H I

turn into

1 A B C
(empty row)
(empty row)
2 D E F
(empty row)
(empty row)
3 G H I

View 9 Replies View Related

How To Add More Rows From Existing Set Of Values

Feb 26, 2014

I have a table with 2 columns and I want to create another table with the same values in these 2 columns but add a new column with values that are different. So for example, I have col A, Col B with values 256 and 14. I want to create a table that has Col A, Col B and Col C with 256 in Col A appearing 60 times, 14 in column B appearing 60 times and new Col C has values ranging from -4 to 55 for each new row. So new table now has 60 rows with same values for Columns A,B but different C value

I can drag or copy paste but when I have 200 unique records in first table, I want to create a second one with 200x60 rows and drag/copy paste is tedious.




View 2 Replies View Related

Creating A New Sheet From An Existing Sheet

Apr 20, 2006

I am trying to figure out the best way to go about creating a new sheet from an existing sheet which has column titles. I guess the best thing for me to do is give an example of what I would like to have accomplished.

sheet A has the following:
title1 title2 title3 title4 title5
a b c d e

I would like sheet B to be created with:
sometitlez sometitley sometitlex
b d e

so basically, I would like to map tiltel2 to sometitlez, title4 to sometitley, and title5 to sometitlex

the reason I am doing this little project is so that I can import the new worksheet to a mysql database with only the necessary information. Please note that sheet A may contain many rows and the converted sheet B will have many rows too.

View 9 Replies View Related

Using VLookup For Non-Existing Sheet?

Jul 21, 2014

I'm creating a blank woorkbook that I'm gonna use for different projects. The other day I've got a solution in this forum for a problem creating the link to a non existing sheet and it works perfectly BUT now I need even more. I have to use formula VLOOKUP in the same cell where I have my IFERROR formula and I can't figure out

More precisely, sheet 'Graphique 2' columns D, F, H... are refferring to sheet 'LOT' F25:F44 (sheet LOT is a model sheet and I'm gonna have more than 50 of it, for different companies). To create some graph in function of date I've added months for the next 3 years in column B sheet Graphique 2, so the money amounts from column F sheet LOT (reffering dates are in column C) have to be copied to coresponding rows in sheet Graphique 2

Normally I think I would have to write:


except that I don't know the names of my future sheet so in the same cells where I should have the VLOOKUP formula I have:


My question is what is the combination of this two formulas?

View 2 Replies View Related

AutoFilter And Add Row To Existing Sheet

Feb 3, 2014

I have 6 working tabs in my workbook (1-10, 2-8, 1-67, 3-16, 2STB, 204th). Each of these sheet has the same number of columns. The first row is a header and the last row in each sheet is used to calculate subtotals and contains no other data. Each row of data is essentially an order that contains an ID number, information on the equipment being ordered, and information on the customer, remarks, and some other tracking information. Each worksheet contains a Column called "Gaining BDE" (Column E). These "Gaining BDEs" are essentially a way of labeling a group of customers. What I need a macro to do is create and maintain a sheet for each of these "Gaining BDEs".

I need excel to look at each of my 6 working tabs and when it sees 1ABCT, for example, copy that entire row of data into a tab called 1ABCT. As I add rows to my 6 working sheets as orders accrue, I would like excel to automatically place a copy of that row in the appropriate sheet. I have a few other sheets in my workbook that I do not want excel to search for data within as they simply serve as references for my VLOOKUP functions. I would also like the cells to be linked so that when I change the Remarks column to reflect "Complete" for example, that change is reflected in these newly created sheets. I have tried working with other peoples copy/paste row VBA code but have not had any luck and I am brand new to VBA so I have a tough time understanding it still.

I'm having some trouble getting my workbook uploaded but a view of what one of my sheets looks like can be seen here: [URL] ........

View 2 Replies View Related

Autofilling Rows In A Column Using Existing Row Data

Mar 9, 2009

I have a column with blank rows inbetween rows with data.

Column Heading

Is it possible to autofill the blank rows with the row that has a value above them, have it skip the next row with a value, recognize the next blank cells and fill them in with the previously skipped row, and so on down the line? How might I go about doing that?

View 4 Replies View Related

Macro Code To Add More Rows To Existing Chart

Jun 17, 2008

I have managed to create something similar to what i am working for using an example from Lacher and Gant Charts. i am now stuck as I can enter more than 40 status as it then gives me an error. The following is the code: Can any1 highlight where i need to make any changes to stop the error from occuring:

Option Explicit
Sub CreateTimeChartData()
Dim vTimeData As Variant
Dim i As Integer
Dim sRoom As String
Dim vLastEndTime As Variant
Dim oSeries As Series
' set up
Application. ScreenUpdating = False
Application.DisplayAlerts = False
' create chart data worksheet
With Worksheets("TimeData"). Range("TimeList"). CurrentRegion
.Sort Key1:="Room", Key2:="Start Time", Header:=xlYes
vTimeData = .Value
On Error Resume Next

View 3 Replies View Related

Updating Existing Data On A Sheet?

Jul 2, 2012

Clicking the Add to DB button will add new items but not existing one.

now, what I want to do is to first, populate the existing TIN and then do the necessary update.

my basis for update is txtTIN.value

Here are the codes anyway:

'This adds the data on the WorkSheet named DBPIT"
Private Sub cmdAdd_Click()
Dim iRow As Long


View 5 Replies View Related

Copyrights 2005-15, All rights reserved