Select Headings of selection(s) to be union with the selection(s) itself?

Issue

By using manual selection(s) , I copy range(s) from workbook to another workbook.
But, how to select the headings of this selection(s) to be union with the selection itself , to fulfill copy and paste in one shot.
Headings are found on first row.
e.g, contiguous selection if I selected Range “B3:D5” , subsequently I need to select ”B1:D1” and union with Range “B3:D5”.
e.g, non-contiguous selection if I selected Range “B3:D5,F3:F5” , subsequently I need to select ”B1:D1,F1” and union with Range “B3:D5,F3:F5”
Copying of contiguous selection and non- contiguous selections (in the same rows) works without problem.
In advance, grateful for useful answer and comments.

Dim wb As Workbook: Set wb = ThisWorkbook    'Source Workbook
Dim srg As Range: Set srg = wb.ActiveSheet.Range(Selection.Address)

Dim wb1 As Workbook: Set wb1 = Workbooks.Add  'Destination Workbook
Dim drg As Range: Set drg = wb1.Sheets(1).Range("A1")

srg.Copy
drg.PasteSpecial Paste:=xlPasteColumnWidths
srg.Copy drg      

Dim r As Range
  For Each r In drg.Rows
  r.WrapText = True
    If r.RowHeight < 40 Then r.RowHeight = 40  
       Next r

Solution

If you want the Selected range Unioned with row one, try this

Dim srg As Range
Dim src As Range
Dim arr As Range
Set src = Selection
For Each arr In src.Areas
    If srg Is Nothing Then
        Set srg = Application.Union(arr, arr.EntireColumn.Rows(1))
    Else
        Set srg = Application.Union(srg, arr, arr.EntireColumn.Rows(1))
    End If
Next

Answered By – chris neilsen

Answer Checked By – Robin (AngularFixing Admin)

Leave a Reply

Your email address will not be published.