Dynamic Range Creation using ActiveCell - Possible?

[Shiseiji]

Hi all. I can think of at least two other ways to do this.

• I can sort the columns so I know the column number
• I can find the cell, select the column, create a named range and sort on that.

And I'm sure there are many more. I'm curious why I've never seen a range starting with ActiveCell and why it doesn't work. I have "guess' " but honestly I struggle to keep objects etc, straight. I'm a hack and freely admit it.

The column is one in a series I want to sort 1st simply to not have any empty or not really empty cells before I convert a text "10-12-31" into a usable date value:

Sub tm_SelectSortCol()
'
' tm_RemoveBtn Macro
'
On Error GoTo Whoa
    '
    '
    Application.Volatile True
    Dim LastRow                       As Long
    Dim lastCol                       As Integer
    Dim rng                           As Range
    Dim thiswksht                     As Worksheet
    Dim thiswb                        As Workbook
    '
    '
    'Worksheets("PIV").Activate
    Set thiswksht = ActiveSheet
    Set thiswb = ActiveWorkbook
    With thiswksht
        LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
        lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    End With
    '
    thiswksht.Sort.SortFields.Clear
    '
    Range("A1").Activate
    Cells.Find(What:="ACTIVATION DATE", After:=ActiveCell, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Select
    thiswksht.Sort.SortFields.Add Key:=Range(ActiveCell, Cells(ActiveCell, LastRow)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
    With thiswksht.Sort
        .SetRange Range("A1", Cells(LastRow, lastCol))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'    
Whoa:
    MsgBox "Error " & Err.Number & " " & Err.Description, vbOKOnly
'
End Sub

As always, thanks much to those who give so freely of their time. I do try to pay it forward.

Ron

 

[Fluff]

Try

    Set Fnd = Cells.Find(What:="ACTIVATION DATE", After:=activecell, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    thiswksht.Sort.SortFields.Add Key:=Range(Fnd, Cells(LastRow, Fnd.Column)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers

 

[Shiseiji]

Nice, thank you! I added:

Dim rng, fnd As Range

Is there a caution for using the named range vs. an RC address?

Thanks again,

Ron

 

[Fluff]

Not sure what you mean by

Is there a caution for using the named range vs. an RC address?

but if you are referring to setting a range variable, rather than using ActiveCell. It's always better not to select/activate as that slows things down.
Also if you use ActiveCell, you have to be very careful when testing/debugging code, because its very easy to change the activecell by mistake.

 

[Shiseiji]

Thanks again. I was trying to ask about setting a range variable vs. "A1:Z26" but you also answered my " . . .I've never seen a range starting with ActiveCell" question.
Right after my 1st posting I had a "duh" moment and tried a different search finding among many other examples:

[/COLOR]Set rng = Range(ActiveCell, Range(ActiveCell.Column))[COLOR=#333333]

The data I'm provided often isn't uniform, hence the "FIND".

R

 

[Fluff]

I regularly use both range variables & addresses, it just depends on what I'm doing.
AFAIK there are no problems/benefits with either