Re: Combining Multiple Columns into one column without any blank cells
No, the macro that I posted
Code:
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 Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next i
End Sub
Re: Combining Multiple Columns into one column without any blank cells
VoG,
When I run it, its only filling in from column B and C
Re: Combining Multiple Columns into one column without any blank cells
Are there blanks in the original data?? i.e. are there any blanks in B, C and D column
Re: Combining Multiple Columns into one column without any blank cells
For me it copied columns B, C and D - the results of my test
<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">A</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">B</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-style:outset;border-color:#f0f0f0; ">D</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >1</td><td > </td><td >b</td><td >c</td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >2</td><td >b</td><td >b</td><td >c</td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >3</td><td >b</td><td >b</td><td >c</td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >4</td><td >b</td><td >b</td><td > </td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >5</td><td >b</td><td >b</td><td > </td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td >b</td><td > </td><td > </td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td >c</td><td > </td><td > </td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td >c</td><td > </td><td > </td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td >c</td><td > </td><td > </td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td >d</td><td > </td><td > </td><td >d</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >11</td><td >d</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >12</td><td >d</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >13</td><td >d</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >14</td><td >d</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >15</td><td >d</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >16</td><td >d</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >17</td><td >d</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >18</td><td >d</td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="border-width:3; border-style:outset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >19</td><td >d</td><td > </td><td > </td><td > </td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </a>
Re: Combining Multiple Columns into one column without any blank cells
there arent any blanks in the original data. Is there anything special I have to do to run the macro? I just have been hitting the macro tab and clicking run
Re: Combining Multiple Columns into one column without any blank cells
The sheet of interest must be selected before you run the code.
Re: Combining Multiple Columns into one column without any blank cells
Ok, I did that. It definitely only copied column B and C for me
Re: Combining Multiple Columns into one column without any blank cells
Can you post a shot of your sheet, for example using Excel Jeanie How to
Re: Combining Multiple Columns into one column without any blank cells
Try this i am sure it will work
Code:
Sub Macro1()
rcB = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
rcC = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
rcD = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
Range("B1", "B" & rcB).Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C1", "C" & rcC).Copy
Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D1", "D" & rcD).Copy
Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If ActiveSheet.Range("A" & r).Value = "" Then
Range("A" & r).Delete Shift:=xlUp
End If
Next r
End Sub
Re: Combining Multiple Columns into one column without any blank cells
Hey VOG, Can you have a look at this code and give me some tips to shorten or make it more compact. I know this is not the cleanest of the code but i would appreciate if you can give me some useful tip as i only have working knowledge macros :)
Quote:
Originally Posted by
norulen
Try this i am sure it will work
Code:
Sub Macro1()
rcB = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
rcC = ActiveSheet.Cells(Rows.Count, "C").End(xlUp).Row
rcD = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row
Range("B1", "B" & rcB).Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C1", "C" & rcC).Copy
Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D1", "D" & rcD).Copy
Range("A" & ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
lr = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If ActiveSheet.Range("A" & r).Value = "" Then
Range("A" & r).Delete Shift:=xlUp
End If
Next r
End Sub
