+ Reply to Thread
Results 1 to 14 of 14

Group rows until an empty row

  1. #1
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Group rows until an empty row

    hi..
    i want to group rows until an empty row comes and this process has to be repeated until the end of the sheet. Can you please suggest code for this....
    Thank u in advance.....

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Group rows until an empty row

    Sure, it will be good if you attach a sample file.

    To Attach a File:

    1. Click on Go Advanced
    2. In the frame Attach Files you will see the button Manage Attachments
    3. Click the button.
    4. A new window will open titled Manage Attachments - Excel Forum.
    5. Click the Browse... button to locate your file for uploading.
    6. This will open a new window File Upload.
    7. Once you have located the file to upload click the Open button. This window will close.
    8. You are now back in the Manage Attachments - Excel Forum window.
    9. Click the Upload button and wait until the file has uploaded.
    10. Close the window and then click Submit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  3. #3
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Group rows until an empty row

    hi arul...im unable to attach...some proxy error is coming....
    Please consider the data is in the follow way...

    s. no id name branch
    1 12 pavani ECE
    2 1234 anu ECE

    3 23423 bala CSE
    4 1231 siva CSE
    5 1231 aswin cSE

    6 12 pavani EEE
    7 1234 anu EEE

    After row#2, an empty line is der,similarly after row#5.
    I want to group the rows 1,2; 3,4,5; and 6,7 considering the empty row as parameter.

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Group rows until an empty row

    I am not able to understand. What do you mean group using the empty row as parameter?

    So how do you want the output to show?

  5. #5
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Group rows until an empty row

    Under Data tab, we have Group option is der na...to group rows....
    In an excel sheet i have 700 rows, in which after say some 'n' rows an empty line is der which divides the above and below rows....(This 'n' is avariable)
    I want to group the rows till the empty line.....Grouping the rows manually is a little bit tedious...so i thought of using macro to do this...
    I recorded macro for this....In that it came like....

    Rows("14:31").Select
    Selection.Rows.group

    I want to parameterize this in such a way that it will group the rows till an empty line comes and this repeats till the end of the sheet

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Group rows until an empty row

    Why not remove the blank lines and try the data->group option?

  7. #7
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Group rows until an empty row

    As i told there are 700 rows in a sheet and after say 30 rows there is an empty line...but this 30 is not constant, it may vary based on data and i have to do this for 4 more excel sheets....

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Group rows until an empty row

    ok got it. Will give you the code shortly.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Group rows until an empty row

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button

  10. #10
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Group rows until an empty row

    Thank you Arlu.....The code is working fine......Thank you for ur time....

    ---------- Post added at 03:57 PM ---------- Previous post was at 03:54 PM ----------

    One more help.....
    when i run this macro, the rows are being grouped but minimized. When i try recording macro for minimizing, it came like
    Range("B13"). Select
    Range("B29"). Select
    Can we automate this also...
    I mean after grouping the rows, it will minimize automatically......

  11. #11
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Group rows until an empty row

    hi Arlu,

    in the above code, how to pass variables for Column #?

    i got the value of the colno using
    Application.WorksheetFunction.Match("Branch")

    And i passed this colno to the below code as
    If .Range(colno & i - 1).Value = "" Then
    but wen i run its throwing "Application defined error"
    Can you please tell any other work around is der

  12. #12
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Group rows until an empty row

    Sorry i didnt see the grouping question. Were you able to solve it?

  13. #13
    Registered User
    Join Date
    06-30-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Group rows until an empty row

    yeah....that was solved...and everything is working fine when i pass "I" in the column number. but i tried to pass a variable instead of Constant...there the error is coming

  14. #14
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Group rows until an empty row

    What is your value of colno at that point?

    If range is giving you a problem, try cells(rowindex, columnindex)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1