CODEWITHSUNDEEP
excelvbacopy-paste
Philip Olsson
Philip Olsson

Reputation: 17

Is it possible to exclude certain columns when copying an entire row in VBA?

I am currently copying a certain rows depending if they contain a certain value. However, I actually only want to copy some of the columns rather then the entire row. What is the simplest way of doing this? MY current code: 

Sub output()

Dim i, Lastrow As Long

Lastrow = Sheets("Dec").Range("A" & Rows.Count).End(xlUp).Row

'Looping from tenth row to last row

For i = 7 To Lastrow

If Sheets("Dec").Cells(i, "AQ").Value = "Shortage" Or Sheets("Dec").Cells(i, "AQ").Value = "Customer" Then
    Sheets("Dec").Cells(i, "AQ").EntireRow.Copy Destination:=Sheets("Action").Range("A" & Rows.Count).End(xlUp).Offset(1)


End If
Next i

Sheets("Action").Activate


End Sub

Upvotes: 1

Views: 2057

Answers (2)

Leung King Tim
Leung King Tim

Reputation: 76

You can use the Union function to unions at least two ranges or cells enter image description here

Private Const column1 As String = "A"
Private Const column3 As String = "C"
Private Const column5 As String = "E"
Private Const column7 As String = "G"

Sub Button1_Click()
    Dim i As Long
    Dim unionRange As Range
    Dim rangeSelection As String

    i = 1
    rangeSelection = column5 & i & ":" & column7 & i


    Set unionRange = Union(Cells(i, column1), Cells(i, column3), Range(rangeSelection))
    unionRange.Select
    unionRange.Copy

    Range(column1 & "2").PasteSpecial    

End Sub

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Imagine the following data:

enter image description here

If you want to copy columns A, B, C and G, H, I you can do that eg with Range("A1:C1,G1:I1").Copy. If you then paste it into A3 using Range("A3").Paste you will end up with

enter image description here

But if you expected it to paste with columns D, E, F being empty like below

enter image description here

Then you must copy and past each range one after the other (you cannot do it in one step)

Range("A1:C1").Copy
Range("A3").Paste

Range("G1:I1").Copy
Range("G3").Paste

Alternatively of course you can copy the whole row and clear columns D, E, F.

Upvotes: 1

Related Questions