Select Multiple cells, Combine in a sequence and paste them in next empty Row in another Sheet

[xs4amit]

Hi,

I am trying to copy multiple (Non Continuous) cells from sheet1 and wants to arrange them in an order (in a single Row) and paste them in next available Row.
For example:

	A	B	C	D	E
1
2		ABC
3		DEF			XYZ
4					LEF
5

<tbody>
</tbody>

now i want to paste them in next sheet like:

	A	B	C	D	E
1	ABC	DEF	XYZ	LEF
2
3
4
5

<tbody>
</tbody>

And next time i need that the values should go to the next available empty Row automatically. Any idea how to do that??

 

[iliace]

Does the consolidation run by rows or columns? In other words, if you initially had:

	A	B	C	D	E
1					
2		ABC			XYZ
3		DEF			LEF
4					
5			

would it go same as you said, or instead like this:

	A	B	C	D	E
1	ABC	XYZ	DEF	LEF	
2

Also, what do you mean exactly by "next time"? How are the cells populated? What triggers the new arrangement?

 

[xs4amit]

Does the consolidation run by rows or columns? In other words, if you initially had:

    A    B    C    D    E
1                    
2        ABC            XYZ
3        DEF            LEF
4                    
5            

would it go same as you said, or instead like this:

    A    B    C    D    E
1    ABC    XYZ    DEF    LEF    
2

Also, what do you mean exactly by "next time"? How are the cells populated? What triggers the new arrangement?

Hi Iliace,

thanks for showing interest. The consolidation will go the same as i said. Next time new values will populate exactly in the same cells.

 

[iliace]

Can you try this and reply with feedback? Run this with your sheet activated, as in the example you showed above.

Public Sub AutoArrange()
  Dim rngArea As Excel.Range
  Dim rngNext As Excel.Range
  
  Dim rng As Excel.Range
  
  Dim i As Long, j As Long
  
  Set rngArea = ActiveSheet.UsedRange
  
  Set rngNext = ActiveSheet.Range("A1")
  
  If Len(rngNext.Value) > 0 Then
    With rngNext.EntireRow
      Set rngNext = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1)
    End With
    With rngArea
      Set rngArea = .Offset(1, 0).Resize(rowsize:=.Rows.Count - 1)
    End With
  End If
  
  For j = 1 To rngArea.Columns.Count
    For i = 1 To rngArea.Rows.Count
      Set rng = rngArea.Cells(i, j)
      If Len(rng.Value) > 0 Then
        rng.Copy rngNext
        rng.Clear
        Set rngNext = rngNext.Offset(0, 1)
      End If
    Next i
  Next j

End Sub

 

[xs4amit]

Can you try this and reply with feedback? Run this with your sheet activated, as in the example you showed above.

Public Sub AutoArrange()
  Dim rngArea As Excel.Range
  Dim rngNext As Excel.Range
  
  Dim rng As Excel.Range
  
  Dim i As Long, j As Long
  
  Set rngArea = ActiveSheet.UsedRange
  
  Set rngNext = ActiveSheet.Range("A1")
  
  If Len(rngNext.Value) > 0 Then
    With rngNext.EntireRow
      Set rngNext = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1)
    End With
    With rngArea
      Set rngArea = .Offset(1, 0).Resize(rowsize:=.Rows.Count - 1)
    End With
  End If
  
  For j = 1 To rngArea.Columns.Count
    For i = 1 To rngArea.Rows.Count
      Set rng = rngArea.Cells(i, j)
      If Len(rng.Value) > 0 Then
        rng.Copy rngNext
        rng.Clear
        Set rngNext = rngNext.Offset(0, 1)
      End If
    Next i
  Next j

End Sub

Hi Iliace,

Your code is fantastic. But it is not what i was in need of (but i learned how to arrange cells in order). I will elaborate my needs a little more:

1. First thing, there are few more cells which need to be arranged and rest of the cells are not empty, they also have data. So the table which need to be arranged will be like:

Name	Amit	Date	23/May/2018	Meeting Status	Scheduled
Emp ID	A1620	Time	12:00:00	Meeting Time	14:00:00
	Meeting ID	Meeting Password
	567493	*12345*

<tbody>
</tbody>

2. Once all the details are filled, this should go to next sheet "Sheet2" and search for next available empty Row and arrange it like:

Name	Emp ID	Date	Time	Meeting Status	Meeting Time	Meeting ID	Meeting Password
iliace	A1593	22/May/2018	11:00:00	Completed	12:00:00	543728	*34567*
Amit	A1620	23/May/2018	12:00:00	Scheduled	14:00:00	567493	*12345*

<tbody>
</tbody>

Hope this helps to understand it better. Thanks a lot for your valuable time and help.

 

[iliace]

Hi Amit - thank you for clarifying.

Let me make sure I am understanding correctly.

Are the headers always consistent? In other words, Name, Emp ID, Date, etc - those will always appear on the source sheet? Matching what it says on Sheet2?

Now, on the source sheet, you will have a header, and then the corresponding piece of data, in an adjacent cell, either below or to the right?

In that case, what do you do when there is ambiguity; for example:

	Time	12:00:00
Name	Amit

How do you propose, in this instance, to determine whether Time is "12:00:00" or "Amit"?