First time poster - VBA Concatenate

[ender777]

Hi guys! I’ve been reading the forums for a few months, but this is my first time posting. I’m pretty new to VBA, and I’ve been trying to figure this one out for days. Basically, some data with long descriptions is pulling back broken into Excel, and I need a way to repair it. The data pulls broken in the same way every time, so it should be pretty easy to write a macro to fix it, but I’m having some trouble with the concatenate portion.

I need to concatenate these broken values from where they show up in Column A, back to the correct cell in Column C, and afterwards delete the rows where those broken descriptions appeared. On a lot of these pulls there are thousands of rows, so doing it by hand isn’t really an option.

The data always pulls with a numeric counter in Column A (1,2,3,4,5…) , so I’m using that to help the macro detect where the descriptions are broken. There are some blank rows in the data breaks so establishing a range is tricky, which is why I’m using ActiveCell Offsets. Here's the VBA I've gotten through so far:

Sub concatExample()

Dim y As Integer
Dim totalCount As Integer
Dim c As Range


y = 1
totalCount = InputBox("Input total number of Items")

Range("A2").Select

    Do While ActiveCell.Value <> totalCount
    
        If ActiveCell.Value = y Then
            ActiveCell.Offset(1).Select
            y = y + 1
        Else
            ActiveCell.Offset(-1, 2).Select
            Set c = Range(ActiveCell) 'make ActiveCell the location where the broken data will wind up.
            ActiveCell.Offset(1, -2).Select
            
                Do Until ActiveCell.Value = y
                
                    'Add value of ActiveCell to starting location above
                    ActiveCell.EntireRow.Delete 'Deletes (cleans up) the row
                    ActiveCell.Offset(1).Select
                
                Loop
                    
            y = y + 1
            
        End If
        
    Loop

End Sub

Any help you could provide would be extremely appreciated! Thanks VBA masters!

 

[sericom]

Give this a try

totalcount = WorksheetFunction.Max(Range("A:A"))
lastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastRow
    If Not IsNumeric(Range("A" & i).Value) Or Range("A" & i).Value = "" Then
        Range("C" & i - 1).Value = Range("C" & i - 1) & Range("A" & i).Value
        Rows(i).Delete
        i = i - 1
    End If
    If Range("a" & i).Value = totalcount Then
        Exit Sub
    End If
Next

 

[ender777]

Sericom that is amazing, thank you so much! That works perfectly.

So if I understand correctly, this takes all of column A as the range, starts at the bottom and works its way up 1 at a time, checking each cell in column A to see if it's a non-numeric or blank, which triggers the concat...that's so smart. I didn't think it would be possible to do it this way because of the sporadic spacing of the blanks in the broken data.

Thank you again, I've spent the last week tinkering with this (and learning a lot while I pulled my hair out in frustration) -- it's so good to see the solution!

 

[sericom]

It starts at the top, and when it finds a blank or non numeric, it concats it to the the line above in C column. Then deletes the row and repeats.

 

[ender777]

Oh, I misunderstood the "xlUp". I think I'm with you now.