Solved: combine mulitple columns

[vzachin]

hi,

i have data in various consecutive columns beginning in A5 that i want to consolidate into Column A5. There will be roughly 60 columns. each columns will have various rows.

2 requests:
how can i loop through each column and consolidate that into column a ?
if the rows exceed the rows limit 63356, then place the data into another sheet. i don't believe i will exceed 2 sheets.

my code so far is for column b only. i'm stuck with the column looping bit.
haven't thought about the row limits yet

Sub test1()
With Sheets("test")
Set Tgt = Sheets("test").Cells(.Rows.Count, "a").End(xlUp).Offset(1)
Set rng = .Range("b5", .Cells(.Rows.Count, "b").End(xlUp))
rng.Cut Tgt
End With

End Sub


thanks
zach

[Zack Barresse]

Hi there Zach,

This seems to work with your test data, and should handle multiple sheets...

Sub CombineDataIntoSingleColumnPlease()
Dim ws As Worksheet, wsData As Worksheet
Dim i As Long, iCols As Long, iRow As Long, iEnd As Long, iSheets As Long
Const iStart As Long = 4
Set ws = ThisWorkbook.Sheets("test")
Set wsData = ThisWorkbook.Sheets("test")
iCols = ws.Cells(iStart, ws.Columns.Count).End(xlToLeft).Column
iSheets = 2
For i = 2 To iCols
iRow = wsData.Cells(ws.Rows.Count, i).End(xlUp).Row
iEnd = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
If iRow + iEnd > ws.Rows.Count Then
ThisWorkbook.Sheets.Add after:=ws
ThisWorkbook.Sheets(ws.Index + 1).Name = ws.Name & iSheets
iSheets = iSheets + 1
Set ws = ThisWorkbook.Sheets(ws.Index + 1)
ws.Cells(iStart, 1).Value = ThisWorkbook.Sheets(ws.Index - 1).Cells(iStart, 1).Value
iEnd = iStart + 1
End If
ws.Cells(iEnd, 1).Resize(iRow - iStart, 1).Value = wsData.Cells(iStart + 1, i).Resize(iRow - iStart, i).Value
Next i
End Sub

Let us know how it works for you.

Edit: Changed one line to get to work with new sheets..

[vzachin]

hi Zack,

thanks for the response & coding.
i still have a slight problem. after data is copied into the new sheet, the subsequent sequence of events fail over here: ws.Cells(iEnd, 1).Resize(iRow - iStart, 1).Value = wsData.Cells(iStart + 1, i).Resize(iRow - iStart, i).Value

it is trying to paste in the original sheet.
how do i change the focus to the new sheet?
i have attached an updated file

thanks
zach

[Zack Barresse]

Hi Zach,

I don't see any file attached. ??

What is the error? On what iteration? An attached file would help. It worked on testing for me. I did edit my post on one revision, when it went to the new sheet, one of the variables was looking at the new sheet and should have been looking at the old sheet. Did you see the change? It was in the iRow variable, changing from looking at the ws variable to the wsData variable..

[vzachin]

hi Zack,

thanks! i must've just missed your edit. works perferctly!

wonder what happened to my attachement?:dunno
thanks again
zach

[Zack Barresse]

Great! Sorry about that small oversight. I tested and it worked fine, I just didn't test to the new sheet right away. Apologies. :)