Modify code

[dougebowl]

I recorded the Macro below and it works great. But, the issue I am seeking help with is from day to day the range of data will change. So, I am looking for help in modifying the code so that the range is not hard-coded, but, for the Macro to determine the last row of the data. In the end, the number of columns will not change, they will be A:D. The number of rows needs to be dynamic as each time the report the code is being applied to is pulled, there will be a different number of rows.

Thanks in advance for any assistance.

Current Code:

Sub LRRValidationMBP()
'
' LRRValidationMBP Macro
'
 
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Count"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Concatenation"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],R[-1]C+1,""1"")"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C2000")
    Range("C2:C2000").Select
    Range("D2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-3]=R[-1]C[-3],CONCATENATE(R[-1]C,"";"",RC[-2]),RC[-2])"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D2000")
    Range("D2:D2000").Select
    Columns("C:D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("A:D").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("MBP_-_LRR_Validation_post_Impor").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("MBP_-_LRR_Validation_post_Impor").Sort.SortFields. _
        Add Key:=Range("A2:A2000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("MBP_-_LRR_Validation_post_Impor").Sort.SortFields. _
        Add Key:=Range("C2:C2000"), SortOn:=xlSortOnValues, Order:=xlDescending _
        , DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("MBP_-_LRR_Validation_post_Impor").Sort
        .SetRange Range("A1:D2000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    MsgBox ("Woohoo, all done!")
End Sub

 

[Fluff]

How about

Sub LRRValidationMBP()
   Dim UsdRws As Long
   
   UsdRws = Range("A" & Rows.Count).End(xlUp).Row
 
   Range("C1").Value = "Count"
   Range("D1").Value = "Concatenation"
   With Range("C2:C" & UsdRws)
      .FormulaR1C1 = "=IF(RC[-2]=R[-1]C[-2],R[-1]C+1,""1"")"
      .Value = .Value
   End With
   With Range("D2:D" & UsdRws)
      .FormulaR1C1 = "=IF(RC[-3]=R[-1]C[-3],CONCATENATE(R[-1]C,"";"",RC[-2]),RC[-2])"
      .Value = .Value
   End With
   With ActiveWorkbook.Worksheets("MBP_-_LRR_Validation_post_Impor").Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
         DataOption:=xlSortNormal
      .SortFields.Add Key:=Range("C1"), SortOn:=xlSortOnValues, Order:=xlDescending _
         , DataOption:=xlSortTextAsNumbers
         
        .SetRange Range("A1:D" & UsdRws)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
    MsgBox ("Woohoo, all done!")
End Sub

 

[dougebowl]

When running the Macro, I receive a "Subscript out of range (Error 9) message. I have attached 2 pics. One of the error message and the other showing that columns C and D were populated as expected, so I am guessing the error is resulting from trying to perform the sort part of the code.

 

[Fluff]

Which line of the code gives the error?

 

[dougebowl]

I don't know. No line is highlighted when the error appears.

 

[Fluff]

If you click on debug in the error dialogue box, it should take you to the line that is causing the error.

 

[dougebowl]

So, odd thing is I tried running the Macro today to determine which line is causing the error and it ran without returning an error and the data set was concatenated together and sorted as desired. At this point, I am considering this completed. Thank you for all your assistance. If I run into any further issues, I will be back.

 

[Fluff]

Glad it's working & thanks for the feedback.

 

[dougebowl]

Well, I spoke a little too soon. I have two datasets and the macro worked perfectly for the first dataset and I replied that it was working. When I tried to apply the macro to the second dataset (both datasets are the exact same structure....2 columns of data to start). When applying the macro to the second data set I just get a "Subscript out of range" error. When I click OK, the dialog box goes away. I am not presented with the 'debug' button to go into the code. I have attached a screenshot of the error message I received. You can see in the dataset that the count was added and the concatenation worked perfectly. The sorting piece of the code did not execute as the the data is not sorted by column A ascending, then column c descending order.

 

[Fluff]

Do you have a sheet called "MBP_-_LRR_Validation_post_Impor" & is that the sheet you are trying to sort?