Modify code

dougebowl

Board Regular
Joined
Feb 22, 2010
Messages
59
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:

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
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

dougebowl

Board Regular
Joined
Feb 22, 2010
Messages
59
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.
 

Attachments

  • Worksheet.jpg
    Worksheet.jpg
    208.7 KB · Views: 6
  • Error Message.jpg
    Error Message.jpg
    192.9 KB · Views: 7

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Which line of the code gives the error?
 

dougebowl

Board Regular
Joined
Feb 22, 2010
Messages
59

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
If you click on debug in the error dialogue box, it should take you to the line that is causing the error.
 

dougebowl

Board Regular
Joined
Feb 22, 2010
Messages
59

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Glad it's working & thanks for the feedback.
 

dougebowl

Board Regular
Joined
Feb 22, 2010
Messages
59
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.
 

Attachments

  • Error Message.jpg
    Error Message.jpg
    181.8 KB · Views: 5

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
Do you have a sheet called "MBP_-_LRR_Validation_post_Impor" & is that the sheet you are trying to sort?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,005
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top