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

    Need VBA to select next row (2002)

    I'm selecting a vertical block of data in a worksheet, CTRL+C, and then click another tab in the sheet. I then select the first available row (2nd row the first time, 3rd row the next time, etc.) and run a macro I recorded that does a Paste Special, Values, Transpose. I'd like to reduce my steps.
    Is it possible, after I've selected the block (it's not always uniform) to have the macro do the copy of the selection, then switch to the other sheet, select the next available row and do the paste special/values/transpose?

    I didn't know how to record this process and don't know enough VBA to write the code for it.

    Thanks in advance.

  2. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need VBA to select next row (2002)

    Is your selected block always one column wide?
    Regards
    Don

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

    Re: Need VBA to select next row (2002)

    Yes, it is. I did some searching around the internet and came up with this option that seems to work.

    Selection.Copy
    Sheets("horizontal").Select
    Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=True

    Any thoughts or is this just fine in your book?

    Thanks,

    Kevin

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need VBA to select next row (2002)

    The only discrepancy is that this will leave Row 1 blank.

    Glad that you found an acceptable answer.
    Regards
    Don

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

    Re: Need VBA to select next row (2002)

    I have column headings in row 1, so that is not a problem.

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Need VBA to select next row (2002)

    You must also ensure that you have something in the first cell of the range you are copying.
    Regards
    Don

  7. #7
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need VBA to select next row (2002)

    Selecting and Copy/PasteSpecial is not needed, unless you want to go to the sheet horizontal.
    <code>
    With Selection
    Sheets('horizontal").Range("A65536").End(xlup).Off set(1, 0).Resize(1,.Rows.Count).Value = Application.Transpose(.Value)
    End With</code>

  8. #8
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need VBA to select next row (2002)

    Would you mind explaining what this line does? I am always lost at the Offset.

    .Offset(1, 0).Resize(1,.Rows.Count).Value = Application.Transpose(.Value)

    Thanks

    regards,
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need VBA to select next row (2002)

    Mike Rickson (welcome to Woody's Lounge, Mike!) isn't online now, so I'll attempt an explanation

    Sheets('Horizontal").Range("A65536").End(xlUp) is the last non-blank cell in column A on sheet Horizontal.
    .Offset(1, 0) is the cell shifted 1 row down and 0 to the right, i.e. the cell below the last non-blank cell.
    .Resize(1, .Rows.Count) refers to a range with that cell as upper left corner, 1 row high and with as many columns as the current selection has rows. Remember, this line is within

    With Selection

    End With

    so .Rows.Count is the number of rows of the selection. In short, Sheets('Horizontal").Range("A65536").End(xlUp).Off set(1, 0).Resize(1, .Rows.Count) refers to the range starting below the last non-blank cell in column A on sheet Horizontal that is the size of the selection but transposed (rows and columns exchanged).
    The code then transposes the values of the cells in the selection and assigns them to this new range. All this is done without changing the selection, whatever the user had selected remains selected.

  10. #10
    Lounger
    Join Date
    Sep 2008
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need VBA to select next row (2002)

    I'm glad it worked for you.

  11. #11
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need VBA to select next row (2002)

    Hans,

    Excellent explaination.

    thanks

    cheers,
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

Posting Permissions

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