CODEWITHSUNDEEP
excelvba
Pavan
Pavan

Reputation: 29

VBA - Select particular Column

I'm looking for an alternative to this code. I need to select 1st and 5th column only

Sub Copy()
        Dim Source As Worksheet
        Dim Target As Worksheet
        Dim valsArray As Variant

        valsArray = Array("A,B,C") '<--| define your values to be filtered on Source sheet column A
        ' Change worksheet designations as needed
        Set Source = ActiveWorkbook.Worksheets("Sheet1")
        Set Target = ActiveWorkbook.Worksheets("Sheet2")

        With Source  '<--| reference Source sheet
            With .Range("A1:A1000")  '<--| reference its range from A1 to A1000
                .AutoFilter Field:=1, Criteria1:=valsArray, Operator:=xlFilterValues   '<--| filter referenced range on its first column with values stored in valsArray
                If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then '<--| if any cell filtered other than
                    .Resize(.Rows.Count - 1, 5).Offset(1).SpecialCells(xlCellTypeVisible).Copy Target.Range("A1") '<--|copy filtered cells skipping headers and paste in target sheet from cell A1
                End If
            End With
            .AutoFilterMode = False
        End With
    End Sub

here in this part i have written .Resize(.Rows.Count - 1, 5) which selects from 1st column to 5th column but i need only 1st and 5th column.

is there any way to get only those column ?

Thanks

Upvotes: 1

Views: 943

Answers (1)

Tin Bum
Tin Bum

Reputation: 1491

Use 2 separate .Resize lines into A1 and B1 

  .Resize(.Rows.Count - 1, 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy Target.Range("A1")
  .Resize(.Rows.Count - 1, 1).Offset(1, 4).SpecialCells(xlCellTypeVisible).Copy Target.Range("B1")

Upvotes: 1

Related Questions