Macro to Combine a List if the adjacent cells are blank

Please Login or Register  to view this content.

Works great Thanks.
Now, if I wanted to have the combined cell (column C) populate onto another worksheet, how would I do that?
So the original list is on worksheetA, but I want the list compiled in worksheetB (instead of in column C)

Edit:
My actual spreadsheet, the "columnA" data is actually in column D and the "column B" data is actually in column Q.
I can't show a screen though as it contains sensitive information.
So basically I need the column Q list to compile if the column D is blank, and then I need the combined data to show up on worksheetB.
I tried manipulating the macro you provided but not sure what the offsets etc would be. Thoughts?

bump, see post above.

If it cannot be copied to the other worksheet (worksheetB, column G), if the data could populate into column R (on the same worksheetA) that would work too.
I can't get the columns D and Q to work though, I think it is combining column E and populating to column F. But I need it to combine column Q and populate to column R (if column D is blank).

EDIT:
I did this and it now combines to column R correctly:

Dim rngArea As Range
For Each rngArea In Range("D2:D" & Range("Q" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Areas
Set rngArea = rngArea.Offset(-1, 13).Resize(rngArea.Count + 1)
rngArea(1).Offset(, 1).Value = Join(Application.Transpose(rngArea), "||")
Next rngArea
Columns("R").AutoFit


BUT I get an error - Runtime Error '13': Type Mismatch for this line:

rngArea(1).Offset(, 1).Value = Join(Application.Transpose(rngArea), "||")

So it works technically but I will have more lines after this so it doesn't help if it errors.

Your modified code looks correct. Apparently there is some issue with the data configuration, but I don't know what it is. If you could post a desensitized copy of the workbook that still errors, I'll take a look.

If you want the results on worksheetB;

then in what column?
do you want the results in consecutive rows?

New questions should probably have their own thread.

Try something like this. It assumes the blank cells in column H are truly blanks and not null string results from formulas.

Please Login or Register  to view this content.