Results 1 to 2 of 2

Thread: Use Resize to select adjacently with multiple cells selected

  1. #1
    Junior Member
    Join Date
    Oct 2013
    Posts
    1
    Rep Power
    0

    Lightbulb Use Resize to select adjacently with multiple cells selected

    Hello,

    I have been trying to use a macro so that after I have already done a "find all' for a keyword, and selected all entries of it in the sheet, it will be able to expand the selection to include not only those cells, but the cells directly below it (1 cell below).... Ex: With cells A1, D1, G1 selected expand the selection to also include A2, D2, G2 (and not deselect the original ones)

    I found a macro posted by Rick on another forum that comes close to this, but the problem is that it copies the text in the bottom cells into the original cells, and this is not what I want. (The data in the below cells is the more important data).
    Here it is,

    Code:
    Sub CopyPasteBack12Columns()
      Dim Ar As Range
      For Each Ar In Selection.Areas
        Ar.Copy Ar.Offset(0, 0).Resize(2)
      Next
    End Sub
    I just wish I could remove that "Ar.Copy" part (and the offset part?) but that just seems to break it in the editor.. any solutions for this?

    Thanks,
    ATJ

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    Code:
    Sub selectionresize()
      Dim Ar As Range, rng As Range
      Set rng = Selection.Areas(1)
      For Each Ar In Selection.Areas
        Set rng = Union(rng, Ar.Resize(2))
      Next
      rng.Select
    '  rng.Value = "A"
    end sub

Similar Threads

  1. fill in cells according to selected optionbutton
    By paul_pearson in forum Excel Help
    Replies: 6
    Last Post: 09-13-2013, 10:26 AM
  2. Select Group Of Cells Using Data Validation
    By rich_cirillo in forum Excel Help
    Replies: 7
    Last Post: 06-09-2013, 05:55 PM
  3. Replies: 3
    Last Post: 05-14-2013, 03:25 PM
  4. Replies: 2
    Last Post: 09-24-2012, 06:24 PM
  5. Replies: 0
    Last Post: 02-27-2012, 01:07 AM

Posting Permissions

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