I have recently tried using Macros to simplify some tasks under Excel 2010 as I am working with unfortunatey huge databanks.
I already found the code I needed to merge duplicate rows and concatening unique data/comments thanks to this life-saving thread: How to combine values from multiple rows into a single row in Excel?
The code was easy to understand for a beginner like me (I do want and try to understand what I am doing instead of just blindly copy-pasting). The only problem I have encountered is that the macro does not seem to stop at the last row, and ends up filling the rest of the excel sheet.
The desired result was obtained as seen in row 4 to 6, but starting row 29... However you can see that starting on row 29, the macro keeps ading ";" in the 10th column.
Here is the code that I have adapted:
Sub merge_dupes_and_comments() 'define variables Dim RowNum As Long, LastRow As Long Application.ScreenUpdating = False RowNum = 2 LastRow = Cells.SpecialCells(xlCellTypeLastCell).row Range("A2", Cells(LastRow, 10)).Select For Each row In Selection With Cells 'if OC number matches If Cells(RowNum, 2) = Cells(RowNum + 1, 2) Then 'and if position and material match If Cells(RowNum, 4) = Cells(RowNum + 1, 4) Then If Cells(RowNum, 5) = Cells(RowNum + 1, 5) Then 'move updated comments up next to the old comment and delete empty line Cells(RowNum, 10) = Cells(RowNum, 10) & ";" & Cells(RowNum + 1, 10) Rows(RowNum + 1).EntireRow.Delete End If End If End If End With RowNum = RowNum + 1 Next row Application.ScreenUpdating = True End Sub
I am not quite sure why it is not stoping and I do not want to input a specific end row as the database I am working with varies every week.
I tried to redefine the last row as:
Dim LastRow As Long With ThisWorkbook.Worksheets("MasterData") 'enter name of the sheet you're working on If Application.WorksheetFunction.CountA(.Cells) <> 0 Then LastRow = .Cells.Find(What:="*", _ After:=.Range("A1"), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).row Else LastRow = 1 End If
But I have noted any changes.
I would be grateful for any help!
Many thanks in advance, KuroNavi