Search code examples
excelvbacopy-paste

Excel VBA for select everything in a column, including blanks

What I am trying to do is to copy one whole column from one sheet to another. I am only getting the first line as a result with what is below:

Sheets("z_raw").Select
Range("A2").End(xlDown).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Sheets("raw").Select
Range("b2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

The first row contains headers which cannot be changed. Any help is appreciated. Thank you!

Solution

  • .SpecialCells(xlCellTypeVisible) will only give you visible cells. If you want to copy the entire column, rather then trying to find only the used area with Range("A2").End(xlDown) you can just do this:

    Sheets("z_raw").Range("A:A").Copy
Sheets("raw").Range("B:B").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

    Please keep in mind that using select and the like is generally regarded as dangerous. This is some good reading on the subject.

    You mentioned in the comments that there are headers in the columns that you wish to keep (I'm assuming in the destination column). To do this, just save the value of the first cell in the column (or wherever your headers are) and replace those values after the paste.

    Dim header As String
Sheets("z_raw").Range("A:A").Copy
header = Sheets("raw").Range("B1").Value
Sheets("raw").Range("B:B").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("raw").Range("B1").Value = header

    Keep in mind that if you have any formulas in B1, they will not be transferred after the paste. The .Value property returns what you see in the cell. If there are formulas simply replace .Value with .Formula.