Novice Macro Amendment Question

[DanLewis]

Hi All,

I am wondering if someone might be able to help me.

I recorded a macro in Excel, it contains the following text:

Range("C2:C10").Select
Selection.Copy

However when it comes to running the macro, I may need to copy more than just C2 to C10, it might be C2 to C20 or even more.

How can I adjust the code to accommodate this? Maybe it involves some kind of code that counts how many lines in column C are populated?

If it helps, no more rows in C will be populated except the ones I need to copy.

Thanks so much in advance.

[GBeats]

Hmm, macro recording is a great way to learn some code on parts of excel lets say that your not familiar with, but it is NOT a place to start learning VBA or any other language for that matter.

i recommend learning some basic principles with vba and this problem you have would be pretty simple to fix.

there is about 20 ways you can do what your asking so im not sure what to tell you. if you have a changing amount of rows that you need to copy then try recording this.

Select the top cell in the row
press ctrl shift and down
then press ctrl c to copy
and see what code that gives you

This will select everything in the column you first selected and also select every row below that until a blank cell is encountered.

[DanLewis]

Hmm, macro recording is a great way to learn some code on parts of excel lets say that your not familiar with, but it is NOT a place to start learning VBA or any other language for that matter.

i recommend learning some basic principles with vba and this problem you have would be pretty simple to fix.

there is about 20 ways you can do what your asking so im not sure what to tell you. if you have a changing amount of rows that you need to copy then try recording this.

Select the top cell in the row
press ctrl shift and down
then press ctrl c to copy
and see what code that gives you

This will select everything in the column you first selected and also select every row below that until a blank cell is encountered.

Thanks, that's exactly what I did when I originally recorded the macro and it's put in C2:C10 (which is the number of rows that were populated when I was doing the example) - problem is that when I run the macro the number of rows can vary, so I wanted to code it to take that into account.

[jdc2000]

Possibly useful links:

https://www.rondebruin.nl/win/s9/win005.htm

https://www.exceltip.com/cells-range...using-vba.html

https://www.excelcampus.com/vba/find...w-column-cell/

https://www.launchexcel.com/excel-vb...cel-worksheet/

[westconn1]

try like

lastrow = cells(rows.count, 3).end(xlup).row
range("c2").resize(,lastrow -1).copy

no need to select the cells to copy

[GBeats]

Thanks, that's exactly what I did when I originally recorded the macro and it's put in C2:C10 (which is the number of rows that were populated when I was doing the example) - problem is that when I run the macro the number of rows can vary, so I wanted to code it to take that into account.

Both the posts (i didnt check the links but im sure they are good) should be good, the example i also left is basically be the same as what 'westconn1' said (he left the actual way we would code it) this should work from the situation you provided.

[SKPglobal]

Select the top cell in the row
press ctrl shift and down
then press ctrl c to copy
and see what code that gives you

This will select everything in the column you first selected and also select every row below that until a blank cell is encountered.