Results 1 to 8 of 8
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Compress a block of data on one sheet to appear in another...

    I wasn't sure how to phrase the title, so did my best.

    On one sheet, I have descriptive information in a number of rows in one column.
    In another column, parallel to those description rows, I have numeric data.
    In some cases, the numeric data is 0.

    On another sheet, I want the descriptive rows where the numeric column is non-zero.
    BUT, I don't want "hole" in the other sheet. I don't want blank rows.

    I want to do the same type of thing for another block of data elsewhere in the first sheet.

    So, if I have

    aaaa 12
    bbb 0
    cccc 10
    dddd 22
    eeee 0

    I ONLY want

    aaaa 12
    cccc 10
    dddd 22 in the second sheet.

    I guessing this is some form of offset, but haven't experimented enough with that to write the proper code.

    Thanks.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    Use Advanced Filter

    K,

    You can use the Advanced Filter option with some VBA code to run it per the attached workbook.
    1. Look at the 3 sheets.
    2. Look at Insert, Names, Define to see the defined range names note I use these names because Excel automatically recognizes them for and places them in the right boxes in the interactive AutoFilter dialog. Since we are using a macro you can use any names as long as you place them in the right spot in the macro.
    3. Use Alt+F11 to view the macro.

    Please post back if you have questions.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks for the suggestion. I just tried to record a macro called "ADJUST", and got an error message saying "unable to record." If I open a new workbook, there's no problem. Any idea what is preventing me from recording in this workbook?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    K,

    By this workbook I assume you mean the one I uploaded? Did you save the workbook before trying to record the macro. I think if you just open the uploaded version you get a read/only copy.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    No, not your workbook. I was trying to record in my original and got that error message for some odd reason.

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by kweaver View Post
    No, not your workbook. I was trying to record in my original and got that error message for some odd reason.
    Sorry, don't have a clue unless you already have a macro by that name.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I couldn't record under ANY name. Very odd.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Here's a non-VBA method.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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