CODEWITHSUNDEEP
excelvba
Mostafa Nabil
Mostafa Nabil

Reputation: 29

Selecting specific columns to according to last row value by VBA

Please i need your support to Select specific columns to according to last row value by VBA

in order to paste this selection only

as i'm working on a project and only what i can do is that select the entire exist data to the last row and copy it normally but there are some columns that i do not want to select them

Any Help Please! This code select the entire data Imported1ws.Range("A2:C" & Imported1LastRow).Copy destination

but actually for example i need to copy only A2:to its last row, C2:to its last row, F to its last row also how could i paste this selection to my destination

Upvotes: 1

Views: 571

Answers (1)

VBasic2008
VBasic2008

Reputation: 54807

Copy Non-Adjacent Columns

  • Application.Intersect
  • Range.Copy
  • Adjust "A:A,C:C,F:F", "Sheet1", "A" (source last-row-column), "2:" (source first row), "Sheet2", "A2" (destination first cell).

A Common Scenario

Option Explicit

Sub copyNonAdjacentColumns()
    ' Constants
    Const sCols As String = "A:A,C:C,F:F"
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet1")
    Dim sLastRow As Long
    sLastRow = sws.Range("A" & sws.Rows.Count).End(xlUp).Row
    Dim srg As Range
    Set srg = Intersect(sws.Range(sCols), sws.Rows("2:" & sLastRow))
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet2")
    Dim dCell As Range: Set dCell = dws.Range("A2")
    ' Copy
    srg.Copy dCell ' A,C,F to A,B,C
End Sub

Upvotes: 2

Related Questions