Combining Multiple Columns into one column without any blank cells

[mdesroc]

I need to be able to combine 3 columns with ranges that may change into one column without any blank cells.

For example, I have columns B, C and D filled with data, but the number of cells in each of those columns will change based on user choices other places in the spreadsheet.

Is it possible to combine the data from B, C, D into Column A automatically and if so how would I go about doing that?

Thank you for any help or advice you can offer.

 

[mdesroc]

Sorry, one more question:

The Columns im copying from are based off formulas. Some of the cells being copied are seen as empty but in actuality contain "". Excel is copying this and showing a value of 0. Is it possible to ignore those cells as well in the copy process?

 

[VoG]

Try

Sub test()
Dim LR As Long, i As Long
For i = 2 To 4
    LR = Cells(Rows.Count, i).End(xlUp).Row
    Range(Cells(1, i), Cells(LR, i)).Copy
    Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next i
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .Value = .Value
End With
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
On Error GoTo 0
End Sub

 

[mdesroc]

Still returning the 0s when I run it.

 

[VoG]

Maybe

Sub test()
Dim LR As Long, i As Long
For i = 2 To 4
    LR = Cells(Rows.Count, i).End(xlUp).Row
    Range(Cells(1, i), Cells(LR, i)).Copy
    Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next i
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value = "" Then Range("A" & i).ClearContents
Next i
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
On Error GoTo 0
End Sub

 

[mdesroc]

No luck. Maybe there is a better way to do it than with copying from the columns, would it help to know what I was trying to accomplish?

 

[VoG]

If 0 is not a valid result from the formulas try

Sub test()
Dim LR As Long, i As Long
For i = 2 To 4
    LR = Cells(Rows.Count, i).End(xlUp).Row
    Range(Cells(1, i), Cells(LR, i)).Copy
    Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next i
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value = 0 Then Range("A" & i).ClearContents
Next i
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
On Error GoTo 0
End Sub

 

[mdesroc]

Vog, you are a genius. That last one did it.

Thank you so much for the help.

 

[VoG]

You are welcome and thanks for the PM.

Incidentally if 0 was a valid result from a formula then a double loop would be required = Slooow - unless anybody knows different.

 

[mdesroc]

fortunately, the list of values is actually student names and grade levels, so yeah 0 is not a valid return.

 

[Mattdim805]

If 0 is not a valid result from the formulas try

Sub test()
Dim LR As Long, i As Long
For i = 2 To 4
    LR = Cells(Rows.Count, i).End(xlUp).Row
    Range(Cells(1, i), Cells(LR, i)).Copy
    Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Next i
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("A" & i).Value = 0 Then Range("A" & i).ClearContents
Next i
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
On Error GoTo 0
End Sub

VoG: I'm trying to find a solution to a similar problem as discussed here in this thread and was hoping you could help me adjust your formula to apply to my specific needs.

Full details on what I need to accomplish can be found at Need Formulas to Hide Partial Concatenate Data and Help Determining Two Other Formulas, along with a downloadable link of the sample document. The particular issue I have in which it seems can be solved by a reworking of your Macro is #3 (aggregating data from an array of cells into one column, sorted by column, from first to last). However, if you could also help me with issue #1 (having the impartial concatenated values disregarded from the aggregation of all data into one column), I would GREATLY appreciate it!

I've already solved issue #2 I had, and I'm confident I can tweak the formula I have setup to count all data in column P (the final aggregated list), minus data from cell P1.

Please let me know if you have any questions, or if you need any further data from me.

Thanks!

- Matt