Copy colums into new sheet

excelnewbie2018

New Member
Joined
Jan 16, 2018
Messages
8
Hello community?

I am trying to copy two not adjacents colums (A and E) into a new sheet by using a VBA code. These two columns contain some data until row 45 (assumption), I just wanna copy the data until the last occupied row and not the graphs and calculation that might be below. Since the data set is updating daily, I would want to write a general code. Is there a way to identify the last occupied row and copy the columns just until that particular row?

Thanks in advance for your help!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
You can find the last occupied row in a column like this (example for column A).
Code:
Dim lr as Long
lr=Cells(Rows.Count, "A").End(xlUp).Row
So to copy everything in that column, you could then use:
Code:
Range("A1:A" & lr).Copy

I just wanna copy the data until the last occupied row and not the graphs and calculation that might be below
That part in red concerns me, though. If there are rows with calculations in that column below the area that you want to copy, then the data you want to copy isn't really in the "the last occupied row", in that column, but somewhere above it. If you have that scenario, how do you differentiate what should be copied from what shouldn't?
 

excelnewbie2018

New Member
Joined
Jan 16, 2018
Messages
8
You can find the last occupied row in a column like this (example for column A).
Code:
Dim lr as Long
lr=Cells(Rows.Count, "A").End(xlUp).Row
So to copy everything in that column, you could then use:
Code:
Range("A1:A" & lr).Copy


That part in red concerns me, though. If there are rows with calculations in that column below the area that you want to copy, then the data you want to copy isn't really in the "the last occupied row", in that column, but somewhere above it. If you have that scenario, how do you differentiate what should be copied from what shouldn't?

Thanks for you reply Joe4.
Maybe I didn't express myself properly. I have a sheet structured in this way: I have a set of Data from cell A1 to J45 (assumption). Below this area (there are a couple of empty rows) I am building some charts and tables to summarize the data. I now need to copy and paste the content of the range a1:a45 and e1:e45 in another sheet because I need to extract further information from bloomberg. I am wondering if it is possible to copy an paste the values without clearly stating a1:a45 because the last row might change.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,139
Office Version
  1. 365
Platform
  1. Windows
Yes, and that is pretty much as I described.

However, might there be data below A45 that you DON'T want to include in the copy?
If so, we will need to account for that.

If there is always at least one blank row after the last row of data and before the stuff you do not want, you could get the last row, assuming that there are no blanks in the middle of your data.
So, to go from cell A1 to the last row in column with data BEFORE the first blank in column A, you could do this:
Code:
Dim lr as Long
lr=Range("A1").End(xlDown).Row
 

Forum statistics

Threads
1,148,293
Messages
5,745,921
Members
423,984
Latest member
sayed manzar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top