Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Grouping Rows (from a certain row number until another row with a title)

Posted on 2011-04-27
6
Medium Priority
?
218 Views
Last Modified: 2012-05-11
Hello Experts,

Have a number of different worksheets that contain data. I would like to group each worksheet from Row 6 until the row that contains text such as "Row Header Data" less 1 row. For example, if in the worksheet named "AREA_1" the text was in row 200, I would group from Row 6 to Row 199.

This needs to be completed for a number of sheets, as named below in the code.

Regards

LK
Sheets("AREA_1").Cells.ClearContents
Sheets("AREA_2").Cells.ClearContents
Sheets("COUNTRY_1").Cells.ClearContents
Sheets("COUNTRY_2").Cells.ClearContents
Sheets("COUNTRY_3").Cells.ClearContents
Sheets("CITY_1").Cells.ClearContents
Sheets("CITY_2").Cells.ClearContents
Sheets("CITY_3").Cells.ClearContents

Open in new window

0
Comment
Question by:lkirke
  • 4
  • 2
6 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35481170
Dear Ikirke,

Could you post an example excel for me to work with...
I can make a small macro that does just would you want..


Kind regards


Eric
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35481196
This should do..

Sub grp()
Dim i As Integer
Dim rw As Variant
i = 6


    Rows("6:6").Select
    Selection.Insert Shift:=xlDown
    While Cells(i, 1) <> "Row Header Data" And i < 1000
    i = i + 1
    Wend
    rw = i & ":" & i
        Rows(rw).Select
    Selection.Insert Shift:=xlDown
    rw = "7:" & i - 1
    Rows(rw).Select
    Selection.Rows.Group
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "Titel"

End Sub
0
 

Author Comment

by:lkirke
ID: 35481247
Hello Eric. Please find attached. It should be grouped from row 6 to row 228 from the example.  
Sample.xls
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 2000 total points
ID: 35481427
Dear Ikirke,

the macro will insert at position 6 a title,
group from 7 to "Row Header Data" less 2 rows as we dont want the dashes included.

I insert a blank row before the dashes so they are not included in the grouping.
Would you like to have summery's or count on the grouping somewhere?

Kind regards

Eric
Sample-1-.xls
0
 

Author Comment

by:lkirke
ID: 35488329
Hello Eric. That's great. No need for summaries. :)

Thank you very much.
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 35489135
Hi Ikirke,

I am glad that I was able to help.

Thanks for the points...

kr
Eric
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question