PDA

View Full Version : Syntax to selecting multiple columns/particular column with its contents in Excel VBA



Kaniguan1969
07-23-2014, 09:46 PM
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,AB 4,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

Bob Phillips
07-24-2014, 01:37 AM
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

snb
07-24-2014, 02:37 AM
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

Kaniguan1969
07-24-2014, 08:06 PM
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.

12024

Kaniguan1969
07-25-2014, 07:29 PM
Hi Guys, The result give me an space column or empty column. please see attached sample. thanks.