Macro copy & paste two selections and convert to one

[Hudco]

I am trying to create a macro to copy say T25 to T50, paste to S25 the copy K25 to K50 and paste to the next empty cell after the contents of S25 to the last filled cell. I then want to remove any duplicates in S25 to end and sort.

So I have data starting at K25 and T25 but the number of cells with data varies. For example there may be data in K25 to K35 and T 25 to T40. I want to copy and paste both sets of data one after the other, remove any duplicates from the combined data set and then sort the data from low to high. The data will be numeric. I then want to copy the new data set to a new column

Thank you in anticipation.

 

[Akuini]

Try this:

Sub a1105708b()
'https://www.mrexcel.com/forum/excel-questions/1105708-macro-copy-paste-two-selections-convert-one.html
Dim va, vb
Dim n As Long, m As Long
Dim d As Object

'Range("S25:S" & Rows.count).ClearContents
va = Range("T25", Cells(Rows.count, "T").End(xlUp))
vb = Range("K25", Cells(Rows.count, "K").End(xlUp))

Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(va, 1)
    d(va(i, 1)) = ""
Next

For i = 1 To UBound(vb, 1)
    d(vb(i, 1)) = ""
Next

With Range("S25").Resize(d.count, 1)
    .Value = Application.Transpose(Array(d.Keys))
    .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
End With

End Sub

 

[Hudco]

Thanks Akuini,

Works except that it is copying data beyond K50 (there is data at K67 to K80 that I do not want to include.

Hudco

 

[Akuini]

Ok, try this one:

Sub a1105708c()
'https://www.mrexcel.com/forum/excel-questions/1105708-macro-copy-paste-two-selections-convert-one.html
Dim va, vb
Dim n As Long, m As Long
Dim d As Object

Range("S25:S76").ClearContents
va = Range("T25:T50")
vb = Range("K25:K50")

Set d = CreateObject("scripting.dictionary")
For i = 1 To UBound(va, 1)
    d(va(i, 1)) = ""
Next

For i = 1 To UBound(vb, 1)
    d(vb(i, 1)) = ""
Next

With Range("S25").Resize(d.count, 1)
    .Value = Application.Transpose(Array(d.Keys))
    .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
End With

End Sub

Note:
This line is to delete the content of column S from previous result. You may remove it if you don't need it.
Range("S25:S76").ClearContents

 

[Akuini]

Here's another way (a simpler one):

Sub a1105708d()
'https://www.mrexcel.com/forum/excel-questions/1105708-macro-copy-paste-two-selections-convert-one.html

Range("S25:S50").Value = Range("T25:T50").Value
Range("S51:S76").Value = Range("K25:K50").Value

With Range("S25:S76")
    .RemoveDuplicates Columns:=Array(1), Header:=xlNo
    .Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
End With

End Sub

 

[Hudco]

Hi Akuini,
Thank you very much. Both worked after I realised the columns were vice versa

Clyde

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.