Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA: Selecting all visible cells

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: Selecting all visible cells

    Hi,

    I have a number of visible rows in my worksheet (and a number of hidden rows). I want to copy over the visible rows only. My data will be of varying lenghts.
    The problem is, when I run my macro it only selects rows 1 & 2 and stops short of selecting all visible rows.
    My Macro looks like the following:

    Range("A1:G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Range("A288").Select
    ActiveSheet.Paste

    When recording the Macro I firstly selected A1 to G1, then held 'Shift' & 'Ctrl' to select all. I had to do this twice to select everything - as doing it once only selected rows 1 & 2. It seems to me that the row in Red above is doing nothing in my Macro, yet it worked when recording the Macro.

    I'd appreciate any help.

    Thanks.

  2. #2
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA: Selecting all visible cells

    Perhaps:
    Code:
    Dim rng As Range
    Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A1:G287"))
    rng.SpecialCells(xlCellTypeVisible).Copy Destination:=Range("A288")
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs

    Blog: Excel Evolution Blog | Twitter: @ExcelEvo | Facebook: Follow me here

    English is a weird language. It can be understood through tough thorough thought, though!

  3. #3
    New Member
    Join Date
    Mar 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Selecting all visible cells

    Thanks for the below.

    I actually realised I need to copy & paste my data to another sheet so I now have the following which is working well:

    Range("A1:G1").Select
    Dim rng As Range
    Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A1:G287"))
    rng.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste

    The problem is that the data is of varying lenght. So it could exceed Row 287 as is called out in the above. Is there any way to ensure it copies all cells?

    Thanks again for the help.

  4. #4
    MrExcel MVP
    Moderator

    Tracy's new best bud
    Jon von der Heyden's Avatar
    Join Date
    Apr 2004
    Location
    Blackboys, East Sussex, UK
    Posts
    10,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA: Selecting all visible cells

    Try;

    Code:
     
    Set rng = Application.Intersect(ActiveSheet.UsedRange, Range("A:G"))
    Regards,
    Jon von der Heyden

    Posting guidelines | Forum rules | FAQs

    Blog: Excel Evolution Blog | Twitter: @ExcelEvo | Facebook: Follow me here

    English is a weird language. It can be understood through tough thorough thought, though!

  5. #5
    New Member
    Join Date
    Mar 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Selecting all visible cells

    Thank you very much for your help.

    Sorted now

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com