VBA: copy whole column content starting for a specific and and the data below it

Question

the thing is I want to copy a certain column but I want to only copy data on a specific cell and get the data below it.

Let say for example, I want to copy Cell C5 and below, this will disregard C1 to C4. Is this possible?

Yes it is possible. Find the last row using [THIS](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920) and then create you range. For Example `Range("C5:C" & LastRow)` and then simply copy it :) — Siddharth Rout, May 12 '15 at 07:43
No. It should not take that long. See the answer that I posted below — Siddharth Rout, May 12 '15 at 09:01

smozgur · Answer 1 · 2015-05-12T08:00:01.143

0

Sheet1.Columns(3).Resize(Sheet1.Columns(3).Rows.Count - 4).Offset(4).Select

This will select entire C column but first 4 cells. It simply take column 3, resize it to subtract first 4 cells and offset the starting cell 4 cell below and select that range. If your range is defined then code could be more optimized.

EDIT for sample code:

Sub copyCells()
Dim sht As Worksheet
Dim rngStart As Range
Dim rng As Range
    Set sht = Sheet1
    Set rngStart = sht.Cells(5, 3) ' this is C5
    rngStart.Select
    Set rng = rngStart.Resize(rngStart.End(xlDown).Row - rngStart.Row + 1)

    rng.Copy Sheet2.Cells(1, 1) ' copy where you need
End Sub

edited May 12 '15 at 08:00

answered May 12 '15 at 07:48

smozgur

1,772
1
15
23

The number of items could vary so the ranged cannot be defined. Thanks for this! – ayasocool May 12 '15 at 07:59
try the sample code I just added. It assumes there is no blank cells in the range. If there is blanks then let me know. – smozgur May 12 '15 at 08:03
@SiddharthRout, if there is no blanks then it is right way. I don't have enough info about the data structure, so I have to make assumptions. There might be other data under the desired data after spaces. That's why I didn't show xlUp from the bottom of the range. And I explained this in my comment. – smozgur May 12 '15 at 08:08
`I don't have enough info about the data structure` Yup and hence to be safe use `xlup` You may want to see the link that I posted below OP's question? – Siddharth Rout May 12 '15 at 08:10
Yea there is no blank spaces @smozgur – ayasocool May 12 '15 at 08:24
@ayasocool: then you can test my code. One thing I forgot to delete after testing in my code: remove rngStart.Select line since it is just for testing purposes. – smozgur May 12 '15 at 10:58

score 0 · Accepted Answer · answered May 12 '15 at 08:58

Further to my comments below your question, here is one way. This will work in all scenarios. Whether you have blank cells or not...

Option Explicit

Sub CopyCells()
    Dim ws As Worksheet
    Dim rng As Range
    Dim sRow As Long, lRow As Long

    '~~> Change this to the relevant worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    sRow = 5 '<~~ Starting row

    With ws
        '~~> Find last row in Col C
        lRow = .Range("C" & .Rows.Count).End(xlUp).Row

        '~~> If the last row < Start Row
        If lRow < sRow Then
            MsgBox "Start Row cannot be greater then last row"
        Else
            '~~> Create your range
            Set rng = .Range("C" & sRow & ":C" & lRow)

            '~~> Copy
            rng.Copy

            '
            ' Do what you want with copied data
            '
        End If
    End With
End Sub

it will not work in all scenarios. Consider there is a separate table/data under the desired range of data. Then it will also get those cells underneath the necessary range. That's what I was trying to refer but obviously OP has tabular data, so this will work without problem in this scenario. — smozgur, May 12 '15 at 11:19
Posting from phone: Obviously if there is another table then it won't. For that then you have to change '.rows.count' to a hard coded value ;) in both cases it is still advisable to use 'xlUp' — Siddharth Rout, May 12 '15 at 11:42

score -1 · Answer 3 · answered May 12 '15 at 08:24

-1

This will copy a entire column (with data) from selection, just paste it wherever you want.

Sub CopyColumnFromSelected()
Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
End Sub

Or (Ctrl + Shift + down arrow) <--- from your desired cell and Ctrl+C ;)

answered May 12 '15 at 08:24

luizast

1

`xlDown` is highly unreliable. Also avoid the use of `.Select` You may want to see [THIS](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Siddharth Rout May 12 '15 at 08:52

VBA: copy whole column content starting for a specific and and the data below it

3 Answers3