Copy colums into new sheet

[excelnewbie2018]

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!

 

[Joe4]

You can find the last occupied row in a column like this (example for column A).

Dim lr as Long
lr=Cells(Rows.Count, "A").End(xlUp).Row

So to copy everything in that column, you could then use:

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]

You can find the last occupied row in a column like this (example for column A).

Dim lr as Long
lr=Cells(Rows.Count, "A").End(xlUp).Row

So to copy everything in that column, you could then use:

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]

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:

Dim lr as Long
lr=Range("A1").End(xlDown).Row