Consulting

Results 1 to 5 of 5

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

  1. #1

    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.

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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

  4. #4
    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.

    capture2.JPG
    Attached Files Attached Files
    Last edited by Kaniguan1969; 07-24-2014 at 10:41 PM.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •