quick help on using a variable to define a range

[jmutsche]

Sub Macro3()

Dim myrng As Range
 
With ActiveSheet
    If .AutoFilterMode Then
        .ShowAllData
    End If
End With
    
ActiveSheet.AutoFilter.Sort.SortFields. _
        Clear
    ActiveSheet.AutoFilter.Sort.SortFields. _
        Add Key:=Range("M1:M51816"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveSheet.AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    x = 2
    Cells(x, 13).Select
    
    Do While Selection.Value <> ""
     Cells(x, 13).Select
    Selection.Value = Selection.Value
    
   
    
    
   x = x + 1
    
    Loop
    
    myrng = "Range(" & "A2" & ":R" & x - 2 & """)"
    Debug.Print myrng
   myrng.Select
   ' Range("A2:R19").Select
   'Cells(x - 2, 3).EntireRow.Select
    
    'Range(Selection, Selection.End(xlUp)).Select

I am getting error 91 object variable or with block variable not set. I don't get it. can someone please help?

[jmutsche]

I am getting error 91 object variable or with block variable not set. I don't get it. can someone please help?

[jmutsche]

ha nvm didn't set my range my bad

[mancubus]

not sure what you're trying to accomplish

Sub Macro3()


    Dim MyRange As Range
    Dim LastRow As Long
    
    With ActiveSheet
        .Range("A1").CurrentRegion.Sort Key1:=.Range("M2"), Order1:=xlAscending, Header:=xlYes
        LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
        With .Range("M2:M" & LastRow)
            .Value = .Value
        End With
        Set MyRange = .Range("A2:R" & LastRow - 2)
        'codes related with MyRange
        '....
        '...
    End With
    
End Sub

[SamT]

Set myRng = Range("A2:R" & CStr(x-2))
'or
Set myRng = Range(Range("A2"), Cells(18, x-2))
'or several other ways

You must SET a variable to an Object.

You can format the code in your post by either selecting all the code and pressing the # icon, or by pressing the # icon and pasting your code in between the new CODE Tags. Finally, you can type the opening and closing tags before and after your code. "Edit" your post to see an example

[SamT]

This is a merged version of two different threads. all the posts in both threads are displayed in chronological order in this thread, regardless of which original thread they were posted in.