Results 1 to 11 of 11

Thread: Row "AutoHide"?

  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Row "AutoHide"?

    I am not sure if this can be done, or how to describe it without excess words, but I'll try.

    Worksheet 1 has 100 rows of part descriptions and quantity ordered (most are zero ordered).
    Worksheet 2 only shows data in rows if the corresponding qty ordered was > 0.

    Question 1: Is there a macro (or some other way) to HIDE all rows with no data in one step?

    Currently I highlight and hide the first blank row(s) and then highlight & repeat (F4) as often as needed. Works, but a little clunky. But if I highlight the displayed grid of "used rows", copy, and then paste it into Outlook Express, I get blank lines corresponding to the hidden blank rows.

    Question 2: Is it posssible to copy ONLY the rows with data to a new Worksheet 3 - a "Paste Special" option that I can't locate, or something?

    My 15-year-old son says "no way" - I am SURE it can be done, but it's probably not worth learning VB (or something else?) to get it done.

    Thanks for reading this.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row

    The autofilter function (Data>Filter>Autofilter) will enable you to select only those rows that are non-blank.

    You can then select the displayed cells, copy them, and then paste.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row "AutoHide"?

    This could probably be done easier using a pivot table, but since I don't know pivot tables, I will give you a couple of VBA procedures. The first procedure below will hide all of the rows on Sheet1 that have a blank in column A. The second procedure will copy columns A and B from Sheet1 to Sheet2 for those rows where column A is not blank.

    <pre>Public Sub HideEmpty()
    Dim I As Integer, iMax As Integer
    iMax = Sheet1.UsedRange.Rows.Count
    For I = 1 To iMax
    If Sheet1.Cells(I, 1).Value = "" Then
    Sheet1.Cells(I, 1).EntireRow.Hidden = True
    End If
    Next I
    End Sub

    Public Sub CopyNotBlank()
    Dim I As Integer, iMax As Integer, J As Integer
    iMax = Sheet1.UsedRange.Rows.Count
    J = 1
    For I = 1 To iMax
    If Sheet1.Cells(I, 1).Value <> "" Then
    Sheet2.Cells(J, 1) = Sheet1.Cells(I, 1)
    Sheet2.Cells(J, 2) = Sheet1.Cells(I, 2)
    J = J + 1
    End If
    Next I
    End Sub
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Row

    Really cool - one of the BILLION Excel features I've never explored. I'll definitely use this technique in the future.

    Unfortunately, in this particular case, when I do the cut/paste into Outlook Express Email, it includes the hidden cells in the paste. I guess I'll have to do a SORT - something I hadn't considered until your words inspired me to think in a new way.

    Thanks a heap.

    Uh oh... I see a VB procedure below from a another guru... I think I'm about to learn more...

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Row

    Thanks Legare...
    WOW! Alt/F8, double-click the first macro, and watch the magic! Then ditto on the second!
    I am dazzled by the help I get - this was almost instant and amazingly educational.
    My son will also be very impressed.
    Thanks, thanks...

  6. #6
    Star Lounger
    Join Date
    Mar 2001
    Location
    New York, USA
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row

    Just one word of caution with the procedure. UsedRange will count the cell range that is used. If your data does not start at A1 the procedure will miss some of the end rows as determined by the difference from A1 to where your data starts. In other words if your used range is A3:A10 UsedRange.Rows.Count will return 8. And the If statement will process from A1 to A8.

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Row

    Using the Auto filter,if possible, does away with the need for macros. Set the filter criteria to non-blank for any one of the columns, and then copy and paste the resultant data. This procedure should copy only visible cells.

    Andrew C

  8. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Row

    Thanks Thorn (that's alliterative!)
    The problem wouldn't occur in my case but I certainly see how it could in the simplified scenario I described.
    Now I have to figure out where I can find a list of VBA commands - I used VBA for the first time last night so I haven't had much time to search, but "help" doesn't seem to help, I don't find them in an appendix in Woody's "Using Office2000" book.
    And I'm quite impressed at it's ease of use.
    Thanks again.

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Row

    You might get some help <A target="_blank" HREF=http://www.vbatutor.com/>Here</A> to help you get started with VBA.

    Andrew C

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Row

    It is possible to have Excel select only the visible cells in the range you selected. First, right-click in the toolbar area, choose the Commands tab, Edit category and scroll down to almost the bottom of the Command list. There is a button, "Select Visible Cells". Drag that button onto the existing (or new) toolbar of your choice, and Close the Customize dialog.

    Then, select the range you want to copy, and click that button. The edges of the cells will change slightly, indicating that you have selected only visible cells. Ctrl-C to copy, then paste as usual into Outlook (or anything else).

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Boston suburb (Acton), Massachusetts, USA
    Posts
    109
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Row

    Fantastic Jim -
    That's the sort of "Paste Special" I figured had to be somewhere.
    Works like a charm within Excel (but not in Word or Outlook Express). One extra (minor) step for me.
    Thanks - I'll use that button a fair amount.
    ---
    So, I've added a new button to my task bar, created and run my first VBA macros (complete with special keystrokes and buttons), added a "VBA tutorial" to my favorites, and I've learned about AutoFilter...
    ---
    Pretty productive learning for a few hours... Thanks again, everyone
    Thanks

Posting Permissions

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