Ask a new question

What does "lastrow = .Cells(.Rows.Count, "Q").End(xlUp).Row" do?

It assigns the lastrow that contains data to "lastrow."  I get that.  But I do not understand a few things about how it does this.  I can not find

any reference to .cells() anywhere.  Where can I find out what optoins there are besides ".Rows.Count?"  what is the "Q" for, and what else might go there, and why?  Basically I would like a reference to this .cell() function, and an explanation of this syntax where a variable is assigned to what appears to be cursor controls? 

I have some knowledge of VBA as it pertains to Access, but accessing Worksheet cells this way is a little confusing to me.

 

Phil

Answer
Answer

This line of code will be used somewhere between a line With ... and a line End With, where ... is a reference to a worksheet.

.Cells is the collection of all cells of that worksheet, and you can refer to a particular cell as Cells(row,column).

.Rows.Count is the number of rows in the worksheet, and thereby also the row number of the very last row in the worksheet (in an Excel 97-2003 workbook 65,536, and in an Excel 2007-2010 workbook 1,048,576).

"Q" stands for column Q in the worksheet.

So .Cells(.Rows.Count, "Q") is the very last cell at the bottom of column Q.

.Cells(.Rows.Count, "Q").End(xlUp) performs the equivalent of pressing End then up arrow in that cell. This moves us up to the last non-blank cell in column Q.

.Cells(.Rows.Count, "Q").End(xlUp).Row is the row number of the last non-blank cell in column Q.

---
Best wishes, HansV
https://www.eileenslounge.com

12 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

.Cells refers to a range object.  Is it within a "With"/"End With" statement?  For example:

With ActiveSheet.Range("P1:S50")

    i = .Cells.Count  ' i = 50 rows x 4 colums = 200 in this case

End With

.Rows.Count provides the number of rows in the range "P1:R50", or 50

"Q" is the column in question

So, .Cells(.Rows.Count, "Q") is the last cell in column "Q" of the range "P1:R50", which would be Q50

the .End(xlUp) part finds the first cell that is not blank above Q50.

the .Row part returns the row for that first non-blank cell

HTH,

Eric

If this post is helpful or answers the question, please mark it so.

5 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated May 15, 2024 Views 33,778 Applies to: