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:
Thanks in advance for any assistance.
Current Code:
VBA 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