Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: Select Range questions on a macro

    Hello,
    I have a sheet that contains a "sort and concatenate" macro. This macro selects three columns and then sorts and concatenates these columns into a new columns. I am trying to figure out how to have the WHOLE columns selected. What do I put as the range for the columns?

    Here is the code:

    Sub SortCaseCaptionandConcatenate()
    '
    ' SortCaseCaptionandConcatenate Macro
    ' Macro recorded 11/18/2005 by palto1
    '
    Cells.Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=RC[1]&"" | ""&RC[2]&"" | ""&RC[3]"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("??:??")
    Range("??:??").Select
    Range("A2").Select
    End Sub

    The "??:??" indicates the area that I need help with. I would like to not have to specify a set number of rows. I would like the macro to look for all rows where there is data. This will be different each time the macro is run.

    Thank you in advance for your help.

    Grace

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    You can define the range initially or you can use Resize. For instance the following would expand the selection by two columns.

    Code:
    Cells(2,1).Resize(0,2)
    BTW, no need (normally) to select a range in order to do something with it.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok a bit late i realise but ive rewritten your code for you
    i think this is the kind of thing your after,

    Code:
    Sub SortCaseCaptionandConcatenate()
    '
    ' SortCaseCaptionandConcatenate Macro
    ' Macro recorded 11/18/2005 by palto1
    '
    
    Dim rRange As Range
        
        Set rRange = Cells.Find(what:="*", searchdirection:=xlPrevious)
        If Not rRange Is Nothing Then
            Set rRange = Range(Cells(2, 1), Cells(rRange.Row, 1))
            Cells.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            rRange.FormulaR1C1 = "=RC[1]&"" | ""&RC[2]&"" | ""&RC[3]"
        End If
    
        Range("A2").Select
    End Sub
    if you have any comments pleasse just let me know ill be about for the next couple of days,

    All the best
    Dave

Posting Permissions

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