Syntax to selecting multiple columns/particular column with its contents in Excel VBA

Hi,

Am trying to automatically copy the contents of the columns B4,C4 combination of
D4&E4,H4 to AU4, AWX4, AX4. Records will until the last not empty row of column B.
then copied to another sheet of the same workbook to the last empty row. Thank you.

Here is my initial code. the issue with code.
1. i cant combine the column D4 and E4
2. I have 9 rows with records but it captures up to 15 rows. basically it should capture until row 9 which it has an exist records.

Code:

---

Sub Button274_Click()
    Dim ws As Worksheet
    Dim my_range As Range
    Set my_range = ThisWorkbook.Sheets("MDX SDS").Range("B4,C4,D4:E4,H4,I4,J4,K4,L4,M4,N4,O4,P4,Q4,R4,S4,T4,U4,V4,W4,X4,Y4,Z4,AA4,AB4,AC4,AD4,AE4,AF4,AG4,AH4,AI4,AJ4,AK4,AL4,AM4,AN4,AO4,AP4,APU4")
    Set ws = ThisWorkbook.Sheets("Test")
    Do
        If Application.WorksheetFunction.CountA(my_range) > 0 Then
            my_range.Copy ws.Range("B" & ws.Rows.Count).End(xlUp).Offset(1, 0)
            Set my_range = my_range.Offset(1, 0)
            Debug.Print my_range.Address
        Else
            Exit Do
        End If
    Loop
End Sub

---

Code:

---

Sub Button274_Click()
    Dim ws As Worksheet
    Dim my_range As Range
    Dim my_area As Range
    Dim lastrow1 As Long
    Dim lastrow2 As Long
   
    With ThisWorkbook
       
        With .Sheets("MDX SDS")
       
            lastrow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
           
            Set my_range = .Range("B4:E4,H4:AU4,AW4,AX4")
        End With
       
        Set ws = .Worksheets("Test")
        lastrow2 = ws.Range("B" & ws.Rows.Count).End(xlUp).Row
        For Each my_area In my_range.Areas
       
            my_area.Resize(lastrow1).Copy ws.Cells(lastrow2 + 1, my_area.Cells(1, 1).Column)
        Next my_area
    End With
End Sub

---

Code:

---

Sub Button274_Click()
  with thisworkbook.sheets(Test").cells(rows.count,2).end(xlup).offset(1)
      .resize(9,5)=thisworkbook.Sheets("MDX SDS").range("B4:E13").value
      .offset(,5).resize(9,39)=thisworkbook.Sheets("MDX SDS").range("H4:AU13").value
      .offset(,44).resize(9,2)=thisworkbook.Sheets("MDX SDS").range("AW4:AX13").value
  End With
End Sub

---

Thank you very much for your reply. Both of your solution has been good and display the required result but i think there's a small modification.
Based on the result given by your codes the column D&E should be concatenated. I will attached a sample data as reference. I notice also it create an space when a column was not inlcuded in the range selection. see sample result. column H should be adjusted to column F. thanks.

Attachment 12024

Hi Guys, The result give me an space column or empty column. please see attached sample. thanks.