Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: VBA to select range

    Hi, I am relatively new to VBA in excel and am having a hard time figuring out how to seach a column for a value and select and copy the range from the first to last match. And move that to column E.

    For ex.

    Column A B C D E F
    Row
    1 1 5 1
    2 1 6 3
    3 1 7
    4 2 6
    5 2 3
    6 2 5
    7 3 2
    8 3 4
    9 4 1
    10

    I want to search column A Range 1: until blank (in this case row 10). For the value in "D1". When it finds the first match i want to select that cell and each cell until the value no longer matches. (Column A will always be grouped together - all the 1's in line the 2's in line the 3's etc.)

    So it would grab "1" the value in "D1" and it would select A1:A3.
    Then Selection.End(xlToRight) so that A1:B3 is now selected. Cut that information and move it to cell E1.

    Then repeat the process for "D2". (still searching column A until Row 10, even though rows 1, 2 and 3 are now blank) Pasting the information below the information that was pasted prior.

    And continue until it reaches a blank in Column D.

    The output would be

    Column A B C D E F
    Row
    1 1 1 5
    2 3 1 6
    3 1 7
    4 2 6 3 2
    5 2 3 3 4
    6 2 5
    7
    8
    9 4 1
    10


    If there is a way to do this it would be greatly helpful. I have been able to make some things work but have been having a tough time. Thanks for the help


    I attached two pictures of the examples above so it was readable.
    Attached Thumbnails Attached Thumbnails Picture1.jpg   Picture2.jpg  
    Last edited by wr43; 02-07-12 at 18:48. Reason: numbers don't line up

  2. #2
    Join Date
    Feb 2012
    Posts
    2
    Here is another way of describing what I am trying to do.

    Select Cell "D1" if blank end sub
    Set Value of "D1" as search variable
    Set range to search as (A1:To first blank)
    Search range for first match
    Select cell
    Serach range for last match
    Select Cell

    Select the range between the first and last match (including matches)
    xl.totheright

    Cut data and move to Column E

    Repeat by select Cell "D2" if blank end sub
    active Cell value as search variable

    Range Same as above (Not until blank but rather still, A1:A11 [which is what the first blank would have been prior to cutting the amounts from Column A1])


    Repeat for D3 and so on until blank

Tags for this Thread

Posting Permissions

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