Consolidating columns of text

QuestionEdit

QUESTION: Good Morning Mr. Ogilvy,

I was asked to update an2002 spreadsheet to2007 and I agreed to do so before I realized that it contained VBA macros. (Ugh!) I know only a little about VBA and I don¡¯t know VBA terminology, so I apologize if I don¡¯t use the correct terms. I¡¯ve managed to get most of the procedures to work in the updated spreadsheet, but I¡¯m stuck on one.

The spreadsheet contains two columns of text. There may be empty cells scattered among the cells in the two columns. These two columns of text must be combined into a single column without the blank cells included. The combined columns should NOT concatenate the text in the cells. An example is below. Column C is the combination of Columns A and B...

Col A Col B Col C abc def abc ghi def jkl mno g pqr jkl stu vwx mno pqr stu vwx

The original2002 spreadsheet used Selection.Consolidate with an array to combine the columns. This procedure does work in2002. I copied the procedure from the original spreadsheet VBA module into the new spreadsheet, but it will not work. I have moved the parts of the procedure around, based on the error messages I received, and now the procedure will work, but only with numbers. It will not combine the columns if they contain text. I haven¡¯t been able to find any reference as to why this would be the case. I¡¯ve also experimented with Range.Consolidate, but wasn¡¯t successful.

So, if you could please tell me how to write a procedure that will combine the two columns, I would be VERY grateful.

Thank you very much for any help you may offer.

Nancy

ANSWER: Nancy,

this doesn't use the consolidate function, but it worked for me:

Sub ConsolidateColumns() Dim lrwA As Long, lrwB As Long, rw As Long Dim lrw As Long, r As Range, cell As Range lrwA = Cells(Rows.Count, 1).End(xlUp).Row lrwB = Cells(Rows.Count, 2).End(xlUp).Row lrw = Application.Max(lrwA, lrwB) rw = 1 Set r = Range("A1", Cells(lrw, "B")) For Each cell In r

 If Len(Trim(cell.Value)) > 0 Then
    Cells(rw, "C").Value = cell.Value
    rw = rw + 1
 End If

Next

End Sub

Tested in Excel 2007.

Test it on a copy of your worksheet until you are satisfied it does what you want.

---------- FOLLOW-UP ----------

QUESTION: First of all, thank you for providing me with the solution. If I enter test data in columns A and B, your solution works perfectly. The problem is, of course, that the data isn't in Columns A and B. I am sorry to be a pest, but this is way over my head. I've tried to figure out where in the procedure to insert the correct columns, but I've only been partially successful. So could you please explain? In the spreadsheet, the first column of data begins in cell J6. The second column of data begins in cell K6. The combined column of data should begin in L6.

Again, I apologize for not including this information in my first request.

Thank you very much for your help.

Nancy

AnswerEdit

Nancy,

No Problem: Hopefully my new choice of variable names and references will make it easier to understand.

Sub ConsolidateColumnsJ_K() Dim lrwJ As Long, lrwK As Long, rw As Long Dim lrw As Long, r As Range, cell As Range lrwJ = Cells(Rows.Count, "J").End(xlUp).Row lrwK = Cells(Rows.Count, "K").End(xlUp).Row If lrwJ < 6 Then lrwJ = 6 If lrwK < 6 Then lrwK = 6 lrw = Application.Max(lrwJ, lrwK) rw = 6 Set r = Range("J6", Cells(lrw, "K")) For Each cell In r

If Len(Trim(cell.Value)) > 0 Then
   Cells(rw, "L").Value = cell.Value
   rw = rw + 1
End If

Next

End Sub