VBA - Find last row with data

[jgscanlon]

I have a worksheet that has formulas in columns A, B, and C in rows 1-10000. If there is no value as a result of the formula, it enters a "" (blank) instead of returning "#N?A".

How do I use VBA to select only the cells that have actual values, and are not blank? In this case, I have 2493 of 10000 rows that actually have data.

Using

Range("A1", Selection.End(xlDown)).Select

does not work, as it selects all 10000 rows; I only want the first, non-blank, 2493 rows.

Thank you!

 

[WarPigl3t]

lastRow = Range("A" & Rows.Count).End(xlup).Row
Do until Range("A" & lastRow).value <> ""
     lastRow = lastRow - 1
Loop
Range("A1:A" & lastRow).Select

I might only thing that might be wrong with the code above is Range("A1:A" & lastRow).Select
You might have to figure it out if it doesn't work but the Do Loop finds that last row that isn't blank.

 

[Rick Rothstein]

I have a worksheet that has formulas in columns A, B, and C in rows 1-10000. If there is no value as a result of the formula, it enters a "" (blank) instead of returning "#N?A".

How do I use VBA to select only the cells that have actual values, and are not blank? In this case, I have 2493 of 10000 rows that actually have data.

Using

Range("A1", Selection.End(xlDown)).Select

does not work, as it selects all 10000 rows; I only want the first, non-blank, 2493 rows.

Try it like this...

LastRowWithValueInColumnA = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row

 

[jgscanlon]

Works PERFECTLY - I didn't even need to edit it!

Thank you so very much!

 

[Rick Rothstein]

Try it like this...

LastRowWithValueInColumnA = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row

I just noticed you said you wanted to select the cells...

Range("A1:A" & Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).row).Select

The above code line assumes there are no blank cells within your data itself with the only blank cells appearing after the last displayed value.

 

[jgscanlon]

I just noticed you said you wanted to select the cells...

Range("A1:A" & Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).row).Select

The above formula assumes there are no blank cells within your data itself with the only blank cells appearing after the last displayed value.

That assumption is correct - the blank cells are all after the last displayed value.

Thank you!

 

[jgscanlon]

Follow-up question. I've copied the previously selected data and pasted the values into a new sheet. Now how to locate the next empty/blank cell in column A of the new sheet (after pasting starting in A1). The number of records is dynamic, and will change each day.

 

[Rick Rothstein]

Follow-up question. I've copied the previously selected data and pasted the values into a new sheet. Now how to locate the next empty/blank cell in column A of the new sheet (after pasting starting in A1). The number of records is dynamic, and will change each day.

If it is only values in the cells (no formulas), you should be able to use the standard method of finding the last cell in a column and then offsetting one row from it. It is not clear what you want returned... the row number or to select the cell itself (I'll assume the latter)...

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select

 

[jgscanlon]

If it is only values in the cells (no formulas), you should be able to use the standard method of finding the last cell in a column and then offsetting one row from it. It is not clear what you want returned... the row number or to select the cell itself (I'll assume the latter)...

Cells(Rows.Count, "A").End(xlUp).Offset(1).Select

That is amazing.

THANK YOU!!