Group/Ungroup rows based on No

[tsatsos007]

Hi everyone...

I have the following challenge, which is rather for presentational issues.

I have like 5-10 spreadsheets that contain eg the same range of products. (say product 1- 20)

My problem is that in some projects i do not use all the lines as it is a template...so sometimes i may need to use 10 out of those 20 lines full of formulas ..and maybe some #NAs... looks bad ..so every time i have to go back and hide them by grouping.

so, what i want to do ..is save time by group/ungroup in ALL spreadsheets (for the specific range where the product row lines are) by saying if() cell is not empty in my main product list sheet :

(so in reality, instead of going every time i use this template to group ungroup for ten twenty sheets ... i figure i can save time by having one sheet as my main eg "product sheet" and by looking if i have entered values in those rows... display the same range in the other sheets.


eg

Main Product list Sheet


#1 product 1
#2 product 2
.
.
.#15 product 15

The range of that 'table' let s assume is 25 rows


------
then, i want the rest of the sheets (could be 10 sheets) in that workbook to have those 15 rows displayed and group the rest of the 10 rows (ie hide them)
bad thing is that it has to be a constant process...sort of like on every change of the main list..and reset obviously previous grouping if that was the case.

if you have ideas ... more than welcomed.

could be macros or not...


I include a simplistic attachment for your convenience...to get an idea..

by the way the products may be split in two groups vertically as in the book..i don t know if that might cause a coding issue.



Appreciate your help

[oeldere]

The order of the columns is always the same?

Your always starting on row 7 (on all sheets).

There are only 4 columns (in all sheets)

if so, that can be done.

After that you can use a pivot table to analyze your data.

[oeldere]

Please Login or Register  to view this content.

[tsatsos007]

Thx..that looks really positive.

to be more specific in my case.. i start from different rows in each spreadsheet. that is i might even have 4-5 'tables' in the same sheet (i need to hide) that i do calculations ...before they end up in say the financial statements.
so, my guess is that one time i have to go manual enter the ranges for each sheet that i want group...which saves me anyhow time for future use!

i have two qns in yr code...as i m quite newbie..

1) Sub integratie_Oeldere_revisted_vs3 ?!? do i go to This workbook as declaration to enter code??
2) i see .Range("C2:C" & .Rows.Count).SpecialCells(4).EntireRow.Delete ...does that mean that the row will be deleted?!? ...

i just want them grouped so i can hide them..not delete them.


in the excel i attach...sheet 2 is like the 'desired result' ..ie grouped for those rows that are meaningless...yet i m trying to make the process dynamic when i use the same template for other projects. the scenario is simplistic as i said ...so the contents in rows to the right are like 5-8 quarterly year data, so don t get bogged down on that end... ( so pivot table is good suggestion for other purpose... but i use those 'tables' as calculations [meaningless sometimes..like binary] before they end up to a complete financial statement format...)

[oeldere]

1) the code is added in your file (click Alt+F11 to open the VBA editor).
after that you see in the left side of the window a directory with the name modules.
if you click on that, you will findthe code I offred.


2) Yes, that will delete the whole row, in which the value in column C is empty => SpecialCells(4).

i will look at the other question later.

[oeldere]

Why did you group the values that are meaningless (0 values)?

3) You don't want to delete them?

4) if you have 58 columns you need to change this code:

Please Login or Register  to view this content.

in this one

Please Login or Register  to view this content.

[tsatsos007]

for some reason i open my file and i do not see any code in there.. not in module or any of the spreadsheets...

quick qn...why do you have to take columns into account ..ie 58 or any number?rows is what i need grouped..

yes..i don t want them deleted because i might add later on another product (in that range) for example ...so that s why i need the procedure dynamic.
the reason i group 0 values is the purpose of this coding... to sort of not go back everytime to 10-20 sheets and do the same thing over and over. to have a 'master' list sheet... and mirror them in the rest of the 10-20 specific ranges to each of those sheets. So, sheet 2 is an example that we have 2 same 'tables' of the Master 'main product' list.... it could be 4-5 more in that same sheet ...to calculate different things for those eg products.

then the same logic applies to sheet 3 which i left ungrouped (just to see that it looks ugly .. and imagine i have 20 sheets similar to sheet 3 to do the same thing....group + hide temporarily [ie unless new products are listed] the zeros...it could be #NAs or some ugly stuff)



so let s say u go now to 'main product sheet' and u name product 9 in B29... what this code should do is...see that in B29 there is sth ie <>"" in that cell ...and reflect/regroup on the spot (dynamically) the correct ranges in sheet 2 +3 the rows [ that i define it to apply for each 'sub'table which is practically the same as the main list...eg in sheet 2 there are 2 tables to apply the regrouping ..in sheet 3 there is 1)

[oeldere]

A) yes you are right, I will post it again, with the code I gave you (I apolagize for that)

B) then excel not aheve tot look in all Columns (so it take longer to get the result => more counting)

C) in that case you run the code again, and all (new data) in the consolidated sheet.
so I think you can delete the empty rows.

D) since I would run the code again, I think that is not a problem.

E) after you done the run of the code again you can make an pivot table, to analyze the data.

[tsatsos007]

...now i see what yr code is doing...which is more complicated than what i need.

you basically reformat to consolidate in a list the data, so that u can manipulate it also later on.

well, my request is rather simplistic... i do not need manipulation of data only hide (by grouping) the rows... i need everything intact ie as is. the way they are....but aesthetic reasons (and possibly to print) just to hide inactive rows (temporarily !)

[oeldere]

So you asking to hide the row if the cells are 0 on the consolidated file?

And what about the empty rows?

[tsatsos007]

the sheet 2 is the answer to yr qn and my request... to group those 0 rows.....but make it a dynamic process, meaning if you go back in the Main list 5 minutes after the inputs, to automatically re-run regroup the rest of the sheets.

[oeldere]

1 step back please.

You don't want to run the code again.

How do you know you get the newest data in your file?

[tsatsos007]

No new data... no manipulation of raw data... no analysis

the existing tables, as shown in the sheets we attached....have a code to group/ungroup/regroup ONLY...no delete in rows or columns
the ONLY condition/difficulty in this code should be to make it a dynamic process (ie on cell change of main list products..re run code) to un group ANY previous formats and Re group...(based on the main list)

[oeldere]

10) Then explain why you want to group those rows.

I suppose it will be for printing reasons.

If so, you can hide those rows and unhide it afterwards.

11)

Please Login or Register  to view this content.

and when should 11) happen?

[tsatsos007]

then the same logic applies to sheet 3 which i left ungrouped (just to see that it looks ugly .. and imagine i have 20 sheets similar to sheet 3 to do the same thing....group + hide temporarily [ie unless new products are listed] the zeros...it could be #NAs or some ugly stuff)

well, my request is rather simplistic... i do not need manipulation of data only hide (by grouping) the rows... i need everything intact ie as is. the way they are....but aesthetic reasons (and possibly to print) just to hide inactive rows (temporarily !)

I can t stress more why i want to group/regroup... already said that 3 times. Aesthetic reasons+saves time group regroup for 20 sheets doing the same process over and over again+printing could be a possibility ...also, challenge to see that code working. Someone else that follows this post might need it for different reasons..so it s a simple useful request applicable to many scenarios

[the code should run on CELL change of the main list.. already said that 2-3 times...]