VBA - Use Range found in cell A1

[n9uns]

Hi,

I have a cell that contains a range which changes dynamically with a dataset. How do I get a code to reference that range rather than hardcoding, for example, "...Range($A$1:$A$2)...".

Better yet, here's the recorded macro:

Sub test()
'
' test Macro
'
'
    ActiveWorkbook.Worksheets("REPORTS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("REPORTS").Sort.SortFields.Add Key:=Range( _
        "AF10:AF209"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("REPORTS").Sort.SortFields.Add Key:=Range( _
        "AG10:AG209"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("REPORTS").Sort
        .SetRange Range("AE9:AM209")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks!

 

[MattH1]

Before I go posting code, tell me if I have the right idea here:
Your # of rows/columns change and you want the code to reflect these changes? Basically in your code AF10:AF209 can be AF10:AF400 and you need the code to automatically recognize this. The same applies to AE9:AM209 possibly being AE9:AX400 or something along those lines.

If this is true, I can come up with some code to help you with this problem.

 

[n9uns]

Before I go posting code, tell me if I have the right idea here:
Your # of rows/columns change and you want the code to reflect these changes? Basically in your code AF10:AF209 can be AF10:AF400 and you need the code to automatically recognize this. The same applies to AE9:AM209 possibly being AE9:AX400 or something along those lines.

If this is true, I can come up with some code to help you with this problem.

exactly!

 

[MattH1]

exactly!

I use these two commands to work my way through documents where I need a row or column count.

Dim RowCount As Long
Dim ColumnCount As Long
RowCount = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
ColumnCount = Sheets(TabName).Cells(1, Columns.Count).End(xlToLeft).Column

Here's how you use it:

 ActiveWorkbook.Worksheets("REPORTS").Sort.SortFields.Add Key:=Range( _
        "AG10:AG209"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

becomes

 ActiveWorkbook.Worksheets("REPORTS").Sort.SortFields.Add Key:=Range( _
        "AG10:AG" & RowCount), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

Let me know if this helped or if you need any other help!
NOTE: This counts the total number of Rows in Column A. If you want to swap it for another Row, just change the "A" in the "RowCount =" line for whatever column you wish to count.

 

[n9uns]

I use these two commands to work my way through documents where I need a row or column count.

Dim RowCount As Long
Dim ColumnCount As Long
RowCount = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
ColumnCount = Sheets(TabName).Cells(1, Columns.Count).End(xlToLeft).Column

Here's how you use it:

 ActiveWorkbook.Worksheets("REPORTS").Sort.SortFields.Add Key:=Range( _
        "AG10:AG209"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

becomes

 ActiveWorkbook.Worksheets("REPORTS").Sort.SortFields.Add Key:=Range( _
        "AG10:AG" & RowCount), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

Let me know if this helped or if you need any other help!
NOTE: This counts the total number of Rows in Column A. If you want to swap it for another Row, just change the "A" in the "RowCount =" line for whatever column you wish to count.

Hey Matt, thanks for the quick response. I have a follow-up question for you. The dynamic range formula is as follows (entered as an array):

=CONCATENATE("$AB$10:",CELL("address",INDEX(AL:AL,MATCH(9.99999999999999E+307,IF(1-(AL:AL=1000),IF(ISNUMBER(AL:AL),AL:AL))))))

Is it as easy as just replacing your rowcount/columncount with the logic above?

Thanks!

 

[n9uns]

Up?