variable as range address

R

r121a947

New member
Joined
Jun 29, 2019
Messages
49
Reaction score
0
Points
0
Excel Version(s)
Office 365
Is it possible to use a variable name as part of a range address? If so, what is the proper syntax?

As is evident, I am very new, and at a loss for this situation. Thank you.
 
Define a variable as range
Code: 
Dim x as range
Set your variable in this case A1 to C5
Code: 
Set x = Range("A1:C5")

If I understand your request correctly, this should resolve your issue.
 
Thank you for your input.

I am trying to loop through a series of ranges. I want (need) a way to have the range address change with each loop. Perhaps I am viewing it all wrong. I am thinking I should be able to address the range as, e.g., (AmyVar:QmyVar), but none of the syntaxes I have tried have worked for this.
 
Show us your code so far so as to get a handle on the context.
 
I am trying to sort each row, left to right.

Code: 
Sub SortRow()
'
' SortRow Macro
'
Dim CurRow As Integer

'
    For CurRow = 1 To 874
    
    Range(M1, AE1).Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range( _
        "MCurRow:ACCurRow"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("MCurRow:ACCurRow")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlLeftToRight
        .SortMethod = xlPinYin
        .Apply
    End With
    Next CurRow
    Application.Run "SpotifyCatalogSpreadsheetTemp.xlsm!SortRow"
    Application.Goto Reference:="SortRow"
    Application.WindowState = xlNormal
End Sub
 
Last edited by a moderator:
A guess (and untested):
Code: 
Sub SortRow()
Dim CurRow As Integer

For CurRow = 1 To 874
  With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SortFields.Clear
    .SortFields.Add2 Key:=Range("M" & CurRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("M" & CurRow & ":AC" & CurRow)
    .Header = xlGuess    'may be better to change this to XlYes or XlNo
    .MatchCase = False
    .Orientation = xlLeftToRight
    .SortMethod = xlPinYin
    .Apply
  End With
Next CurRow
'Application.Run "SpotifyCatalogSpreadsheetTemp.xlsm!SortRow" 'this might be dodgy if it's calling this very macro.
Application.Goto Reference:="SortRow"
Application.WindowState = xlNormal
End Sub
 
Thank you. Works great!

I think I was missing that middle & in the versions I was writing.

This will help in several other projects.

Thanks, again.
 
You must log in or register to reply here.
Back
Top