Select row after LastRow 1

[RP1America]

I would like to select the entire row that is one after the last row with data in it.

    Dim LastRow As Object
    
    Set LastRow = Sheet1.Range("A65536").End(xlUp)
    LastRow.Offset(1, 0).Select
    Selection.Copy
    Set LastRow = Nothing

Select isn't producing the result that I am intending in this code. Any thoughts?

Thanks!

 

[SkipVought]

hi,

Select isn't producing the result that I am intending

What were you intending that is not happening?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

BTW, I am aware that with the code as is it should only be selecting the first cell.

 

[RP1America]

I am expecting the first cell directly below the last row that contains data to become selected.

The overall intent is to copy the row directly below the last row that contains data.

 

[SkipVought]

    Sheet1.Range("A65536").End(xlUp).offset(1).entirerow.copy

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

This is a very strange objective, as "the entire row that is one after the last row with data in it" is EMPTY, so why would you COPY and EMPTY ROW?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Thanks, Skip!

Youdaman!

 

[RP1America]

The row that is directly under the last row that contains data does contain formulas in some cells (not in A). Therefore I am copying the "blank but with formulas" row, entering data into that row, then pasting another row under that one that will now contain the formulas. I am using a For Next loop to do this for each instance of new data (and therefore new row).

 

[SkipVought]

BTW, your code is Excel 97'-2003 specific, as 2007+ has hundreds of thousands more rows...

with Sheet1
   .cells(.cells.rows.count, "A").End(xlUp).offset(1).entirerow.copy
end with

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

I'm having trouble understanding how to paste later in the code. I imagine if I were wanting to paste immediately, I could use Destination directly after copying.

How can I paste after additional code has run?

 

[RP1America]

Ahh...PasteSpecial did it.

However, when it does paste, I am also getting the data that was just entered AFTER the original Copy.

1) Copy row (with no data - but has formulas)
2) Enter data into same row
3) Paste row (with no data - but has formulas)

 

[RP1America]

The three steps above are what I am intending to happen.

Instead, I am getting:

1) Copy row (with no data - but has formulas)
2) Enter data into same row
3) Paste row (HAS DATA)

 

[SkipVought]

I have no idea what you are doing.

When I identify the next empty row, IT IS EMPTY!!!

But your row is NOT EMPTY??? It has FORMULAS???

This is NOT what I would refer to as a best and accepted proctice in Excel.

Please explain!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

I apologize for my lack of knowledge and experience in Excel VBA. However, that is partly why I am here requesting help. Additionally, I appreciate the best and accepted practices and plan to adhere to them when I become aware of them. Yet, if I don't know, I can't adhere to them.

I am learning - and making mistakes as I go helps me to learn and improve.

I would like to state that I never once mentioned needing to identify an empty row. Upon you questioning, I stated that the row does contain some formulas in some cell. However, I am aware that the code that is finding the last "row" with data is actually only finding the last CELL A with data. Once I find that cell(row), I offset by one to copy the next row (that contains formulas - but not in cell A). I then enter data into this row. I would like to then create a new row under this by pasting what I originally copied (row that contains formulas in some cells).

I know there are many different and better ways to do things, and as I said, I am still learning.

Please don't mistake my lack of knowledge for lack of intelligence. I am very willing to learn.

 

[SkipVought]

But that is what I am asking...

Why do you have rows with ONLY FORMULAS and no data? THAT is what is NOT a best and accepted practice.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

The spreadsheet is a template.

Everyday a report in generated where the data must be scrubbed and placed into this new spreadsheet (template) where some cacluations are done.

For each instance of a condition on the original report, a new line must be added to the template where data based on that condition will be inserted.

 

[SkipVought]

You do not need prefilled rows with formulas! That practice messes up stuff, like what you are struggling with now.

I assume that you have Excel 97-2003.

Change your sheet1 table to a Data > List. As you ADD DATA ROWS to your table, like adding data in column A, the formulas will AUTOMATICALLY PROPOGATE to the row containing NEW DATA. In Excel 2007+ it is Insert > Tables > Table

Bottom line, the contiguous rows in your table should be no more than rows that contain data!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}