Find last row with data

[rollingzep]

Hi,

I import data from a large text file daily with over 200,000 rows to a workbook, WB1. In my VBA code, I import all the fields and then delete the fields and format them.
Of the 30 odd fields, I finally get the data on the 3 columns, This Workbook, i use for the backend data. On column F, G and H, I have 150 rows of saved data, which are
constant and which I copy and add to the imported rows, A, B and C manually.
is there a way to do this via code? I need to copy and paste this data after the last identified row of Col A.

I know to derive the last row containing data, using Range("A" & Rows.Count).End(xlUp).EntireRow.Select.
How to identify the next row to this last row and add data from cols, G to H?

TIA

 

[Joe4]

There are various ways. You can move to the first blank row after the last row using OFFSET, i.e.

Range("A" & Rows.Count).End(xlUp).EntireRow.Offset(1, 0).Select

If you are going to be using it in various places, it may be easier to capture the last row number in a variable like this:

Dim lr as Long
lr = Range("A" & Rows.Count).End(xlUp).Row

Then, if you wanted to populate columns G and H in the row just below the last row with some values, you could do something like:

Range("G" & lr + 1).Value = ...
Range("H" & lr + 1).Value = ...

 

[Legacy 143009]

If I were you, I would use some combination of Offset() and Resize().

Range("A" & Rows.Count).End(xlUp).Offset(1, 6).Resize(, 2).Select

 

[rollingzep]

There are various ways. You can move to the first blank row after the last row using OFFSET, i.e.

Range("A" & Rows.Count).End(xlUp).EntireRow.Offset(1, 0).Select

If you are going to be using it in various places, it may be easier to capture the last row number in a variable like this:

Dim lr as Long
lr = Range("A" & Rows.Count).End(xlUp).Row

Then, if you wanted to populate columns G and H in the row just below the last row with some values, you could do something like:

Range("G" & lr + 1).Value = ...
Range("H" & lr + 1).Value = ...

I just want to copy and paste the data in rows F to H below the last row of col A to C.
So I use this Range("A" & Rows.Count).End(xlUp).EntireRow.Offset(1, 0).Select and then do a copy and paste of col F to H?

 

[Joe4]

I just want to copy and paste the data in rows F to H below the last row of col A to C.

That is not quite a clear statement. Are you saying that you want to copy the values from columns F-H from the last row to columns A-C to the row below the last row?

 

[rollingzep]

That is not quite a clear statement. Are you saying that you want to copy the values from columns F-H from the last row to columns A-C to the row below the last row?

As I mentioned in my first post, rows F to H contain 150 rows of saved data, which are constant,
I import data daily to the columns A to C. I need to add these 150 rows of data to the cols A to C, after identifying the last row of data on col A.

 

[Joe4]

Without seeing/knowing the structure of your files, it is still not clear to me. And it doesn't help that really mixing your nomenclature here (F to H are columns, not rows).

Can you walk us through a detailed example, maybe sharing some screen print so we can see exactly how everything is structured.
Be sure to include actual range addresses.

rows F to H contain 150 rows of saved data

What range, exactly is this?
Are columns A-C of these same rows already populated or not?

I import data daily to the columns A to C. I need to add these 150 rows of data to the cols A to C, after identifying the last row of data on col A.

What range, exactly is this being pasted to?

 

[rollingzep]

Without seeing/knowing the structure of your files, it is still not clear to me. And it doesn't help that really mixing your nomenclature here (F to H are columns, not rows).

Can you walk us through a detailed example, maybe sharing some screen print so we can see exactly how everything is structured.
Be sure to include actual range addresses.


What range, exactly is this?
Are columns A-C of these same rows already populated or not?


What range, exactly is this being pasted to?

For example, this is the data which I import daily and which keeps changing.

and to the above I need to add data from F to H, which is saved and constant data. I need to add after row447923, which is identified as last row of data.

 

[Joe4]

OK, I will try one last time.

In your example, please specify the EXACT ranges (full, complete range addresses) of where data is being copied from/to.
For example, copy from F1:H150 to A447924:C448704

 

[rollingzep]

OK, I will try one last time.

In your example, please specify the EXACT ranges (full, complete range addresses) of where data is being copied from/to.
For example, copy from F1:H150 to A447924:C448704

While F1:H150 is constant, A447923 is not and will be determined using Range("A" & Rows.Count).End(xlUp).EntireRow.Select, after my daily text file import