Select Range questions on a macro

palto1 · #1 (**permalink**) 12-06-05, 08:34

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

shades · #2 (**permalink**) 12-06-05, 09:36

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.

DavidCoutts · #3 (**permalink**) 12-12-05, 07:59

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

Internet Services

Web Development

Digital Marketing

Consumer Tech