Macro to convert multiple columns in one column without taking input from user and the rows and column value may vary

[siddo]

1​	9​	20​	27​	40​
2​	10​	22​	28​	41​
3​	13​	23​	30​	44​
6​	14​	24​	33​
7​	16​	25​	36​
8​	19​	26​	37​

 

[Joe4]

So, do you just want to move all the data into column A?
What row does the data start on?

 

[Joe4]

If my assumptions are correct, and the data starts on row 1, this code would do what you want:

Sub MoveData()

    Dim lr As Long
    Dim lc As Long
    Dim c As Long
    
   Application.ScreenUpdating = False
    
'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Exit if only one column of data
    If lc < 2 Then
        Exit Sub
    Else
'       Loop through all columns
        For c = 2 To lc
'           Cut/paste data
            Range(Cells(1, c), Cells(lr, c)).Cut Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
        Next c
    End If
    
    Application.ScreenUpdating = True
    
End Sub

 

[siddo]

Thank you it works but few things.
1. My Data starts with Row B1 for that I have made adjustments.
2. Just wanted to know is there a way I can specifically define the cell number to paste the data

 

[Joe4]

2. Just wanted to know is there a way I can specifically define the cell number to paste the data

What do you mean?

You don't want to move all the other columns under the first?
Are you saying that you want to paste the list into a whole different location altogether?
If so, do you actually want to "cut" (which actually moves the data), or just "copy" (which will leave the original data alone)?

 

[siddo]

What do you mean?

You don't want to move all the other columns under the first?
Are you saying that you want to paste the list into a whole different location altogether?
If so, do you actually want to "cut" (which actually moves the data), or just "copy" (which will leave the original data alone)?

I just want to cut the data and paste in to a whole different location...Thank you

 

[Joe4]

I just want to cut the data and paste in to a whole different location...Thank you

OK, that was not clear from your initial post.

Try this:

Sub MoveData()

    Dim lr As Long
    Dim lc As Long
    Dim c As Long
    Dim ad As String
    
    Application.ScreenUpdating = False
    
'   Prompt user for address to paste to
    ad = InputBox("What is the address of the cell you want to paste the first row of data to?")
    
'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Find last row in column B with data
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all columns, starting with column B
    For c = 2 To lc
'       Cut/paste data
        Range(Cells(1, c), Cells(lr, c)).Cut Range(ad)
'       Find next blank row to paste to
        ad = Range(ad).End(xlDown).Offset(1, 0).Address
    Next c
    
    Application.ScreenUpdating = True
    
End Sub

 

[siddo]

Apologies for not being clear in my response....But as I mentioned earlier I do not want input from user so if you could help me paste the offset value in Cells(1,1) of same sheet

 

[siddo]

Apologies for not being clear in my response....But as I mentioned earlier I do not want input from user so if you could help me paste the offset value in Cells(1,1) of same sheet

I got it covered....Thank you so much for your prompt response.

 

[Joe4]

You are welcome.