Combine rows with identical fields of information

Question

I have a workbook (C:\DOORS.xlsm) with the following data:

 A           B            C               D
100       Type A     Description1      Remarks1
102       Type A     Description1      Remarks1
103       Type C     Description2      Remarks2
104       Type D     Description3      Remarks3

I'm still learning VBA and would like to know what i should research to be able to sort the above data to look like this.

    A           B            C               D
 100, 102    Type A     Description1      Remarks1
   103       Type C     Description2      Remarks2
   104       Type D     Description3      Remarks3

I think I would use a loop to go thru each cell and verify if it matches another entry, still trying to wrap my head around the concepts of loops,but I was told to avoid them if possible. Then i believe you would use something like a sort function inside the loop? Let me know if i can improve my question or clarify anything. I'm trying to ask good questions and would love some constructive criticism =).

original title: Trying to learn VBA and need help pointing me in the right direction

Answer 1

1. Before writing any code, go to the VBE's Tools ► Options and put a checkmark beside Require Variable Declaration on the Editor tab. This will save you debugging time if you misspell a variable when typing your code. This option will be remembered and the following will be automatically placed into the Declarations area of each new module code sheet you create.
    

   Option Explicit

2. Start creating your sub procedure and type out any variables that you already know you are going to need..
    

    Sub data_Rollup()
       Dim rw as Long, strA as String, strB as String
       'lots to go here
    End Sub

3. Define your working environment with one or more With ... End With statements. Use the Range.CurrentRegion property to isolate your row and column references to the 'island' of data originating in the worksheets A1 cell.
    

   With Worksheets("Sheet1")
       With .Cells(1, 1).CurrentRegion
           'all of the working code here
       End With
   End With
   

4. Range.Sort your data on column A first then columns B, C and D. You can only sort in three key columns at a time so you will want to sort column A first to get those ordered then B, C and D to match the duplicates. You should know whether your data has column header labels or not. Do not rely on Excel to xlGuess.
    

       .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
                         Orientation:=xlTopToBottom, Header:=xlYes
       .Cells.Sort Key1:=.Columns(2), Order1:=xlAscending, _
                         Key2:=.Columns(3), Order2:=xlAscending, _
                         Key3:=.Columns(4), Order3:=xlAscending, _
                         Orientation:=xlTopToBottom, Header:=xlYes

5. Loop from the bottom to the top. You are going to want to get rid of rows that you combine with identical rows and if you progress from the top to the bottom while deleting rows you run the risk of skipping over a row. e.g. you delete a row and everything shifts up; you increment (loop) and move on to the next row but in reality, you've skipped over the one that was just shifted up.
    

       For rw = .Rows.Count¹ To 2 Step -1
           'more to come here
       Next rw
   

6. Concatenate the strings from columns B, C and D together for the current row and the row above. Use either the LCase function or the UCase function to remove case-sensitivity from the text string comparisons. If a match between the three information columns is found, Join the row's column A contents with a delimiter then use the Range.Delete method to permanently remove the row.
    

       strA = Join(Array(.Cells(rw, 2), .Cells(rw, 3), .Cells(rw, 4)), ChrW(8203))
       strB = Join(Array(.Cells(rw - 1, 2), .Cells(rw - 1, 3), .Cells(rw - 1, 4)), ChrW(8203))
       If LCase(strA) = LCase(strB) Then
           .Cells(rw - 1, 1) = Join(Array(.Cells(rw - 1, 1), .Cells(rw, 1)), ", ")
           .Rows(rw).EntireRow.Delete
       End If

7. When you think it looks right, use Alt+D+L to compile. Deal with undeclared variables or missing clause closes as necessary.
8. When it compiles without error, place the cursor anywhere within the procedure and start tapping F8. This will step you through the procedure roughly line-by-line so you can see what is happening. Use the Watch Window and hover the cursor over variables to see current states and values. If you are confident with a section, place the cursor on a working code line below the section and tap Ctrl+F8 to *Run-to-cursor.
9. When it runs through reliably without error you can look into adding runtime optimizations like Application.ScreenUpdating property and others.

Nothing to it.

---

¹ _{Since you are working within the Range.CurrentRegion property, the .Rows.Count is the total number of rows in the .CurrentRegion.}