Results 1 to 10 of 10
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts

    Excel2007:vba to copy rows, including hidden rows

    Hi

    I am using something like this:

    Rows("21:297").Copy zDest.Rows(21)

    ..but any filtered or hidden rows in the source row range are not being copied to the destination location.
    It seems to copy 'visible' rows only.
    I can't turn autofilters off in the source range, or unhide any hidden rows, before I run this copy command.

    Any suggestions????

    zeddy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Long-winded approach may work.

    Loop through the individual cells (or perhaps the explicit rows may work also, I leave that to you to test) and copy them. Or if nothing else you could place the values (or formulas if appropriate) in the cell without the individual copying.

    Steve

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy,

    Perhaps something like this might apply

    Code:
    Public Sub CopyAllRows()
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Dim rng As Range
    Dim oRow As Range
    Set rng = ActiveSheet.Rows("1:" & LastRow)
    nRow = 20
    For Each oRow In rng
        oRow.Copy
        Rows(nRow).Select
        ActiveSheet.Paste
        nRow = nRow + 1
    Next oRow
    End Sub
    It will copy the hidden rows as well as the visible rows of a filtered list

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

    Am I missing something or did you forget to change the ActiveSheet before entering the loop?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    No, my sample simply points out one possible way to copy the hidden rows. I will leave it to the capable hands of Zeddy to integrate it into his project if it meets his needs. Good thought however. The 'Achilles heal' of this approach is if the hidden rows are at the end. They will not be picked up as the last row. If the last row is known then it can be hard coded else overshooting the last row then checking upward to look for the presence of data as the last row may be one work around. Tried to employ with Used Range, CurrentRegion, and SpecialCells but they did not pickup the filtered out rows. Maybe someone else may have better luck

    Maud
    Last edited by Maudibe; 2014-01-20 at 06:03.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi

    Many thanks for the responses.
    Really appreciated.
    I was admitted to hospital yesterday, so I'm sending this today from my hopital bed.

    The reason I am copying entire rows is because I need to preserve cell formatting, any formulas, cell comments etc.
    I know what (variable) row range is required. The rows are in a consecutive range but can included hidden rows.
    As I said before, I cannot 'unfilter' the source rows (for a number of reasons).

    I also tried using 'current region', but that also, by default, copies visible row data only.

    I was hoping for a fast effient single-block method rather than looping through rows.
    Thought there might be a trick or other vba magic that might be missing.

    But I'm still working on it.
    But not exactly right now.

    Logging off before my wife catches me.

    zeddy

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Here's to a speedy recovery Zeddy

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If the brute force of individual copies may be a pain (and slow), an easy option, may be to:
    copy the entire source sheet (creating "Source2")
    unhide and unfilter all rows in Source2
    than copy and paste from source2 to the destination
    Delete source2

    Have a good rest and recover quickly.

    Steve

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    zeddy (2014-01-25)

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Steve

    ..why didn't I think of that.

    zeddy

  11. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    ..why didn't I think of that.
    Maybe it was too obvious... I did not think of it right away either.

    Steve

Posting Permissions

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