How to copy variable range vba

Question

My table contains variable number of rows and three columns (A:C) that I am interested in

    A   B   C  D
1  xx  xx  xx  xxx
2  ....
3  ....
4  ...

I need to copy from WorkSheet1 to WorkSheet2

ws2.Range("A1").Value = ws1.Range("A1:C4").Value

The problem is that I do not want to hardcode C4 , because it can be C5 or C20 . How can I account for possible variable number of rows.

PS : I cannot use Range("A1").CurrentRegion because this will select more columns than needed, i.e. column D values will also get selected. Although it will select the correct number of rows

Is your table a ListObject? If so, this is super easy. If not, you need some VBA to determine where the table "ends", which can be a little more difficult depending on the layout of your worksheet. — David Zemens
– David Zemens, Commented Apr 26, 2014 at 2:50

David Zemens · Accepted Answer · 2014-04-26 02:53:58Z

4

There is more than one solution to most problems, but since CurrentRegion method returns the right number of rows (but the wrong number of columns), you could do something like this assuming you always need THREE columns.

Dim rng as Range
Set rng = ws1.Range("A1").CurrentRegion.Resize(,3)

ws2.Range(rng.Address).Value = rng.Value

answered Apr 26, 2014 at 2:53

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Zemens Over a year ago

once you start using Resize and Offset your world will never be the same :)

jmstoker · Accepted Answer · 2014-04-26 02:59:19Z

2

I'm making an assumption that the last row in D will be equal to or less than the last row of A:C. I'm also assuming that the last row is determined by the last row that contains data.

Sub CopyColumnAtoC()
    Dim lastRow As Long

    lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    ThisWorkbook.Worksheets(1).Range("A1:C" & lastRow).Copy

    ThisWorkbook.Worksheets(2).Range("A1:C" & lastRow).PasteSpecial xlPasteValues
End Sub

edited Apr 26, 2014 at 2:59

answered Apr 26, 2014 at 2:54

jmstoker

3,5051 gold badge25 silver badges36 bronze badges

2 Comments

David Zemens Over a year ago

Typo (A2:C...) should be A1. Also, you can avoid actively using the .Copy method to copy/paste values, per OP's method of assigning the range's .Value. Cheers!

jmstoker Over a year ago

@David Thanks for pointing out the typo. I explicitly used the copy and paste as a safe guard in case my assumption about column D being longer were wrong. If Column D is longer, then the corresponding rows of A:C could possibly contain #N/A

ob1quixote · Accepted Answer · 2014-04-26 03:20:04Z

1

You can specify just columns in a range. So

Sheet2.Range(Sheet1.Range("A:C").Address).Value = Sheet1.Range("A:C").Value

will copy columns A through C regardless of the rows in each column, i.e. if Column A has 8 rows, B has 6, and C has 11 it will still work.

answered Apr 26, 2014 at 3:20

ob1quixote

3991 gold badge3 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to copy variable range vba

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related