Meaning of .Cells(.Rows.Count,”A”).End(xlUp).row

by

It is used to find the how many rows contain data in a worksheet that contains data in the column “A”. The full usage is

 lastRowIndex = ws.Cells(ws.Rows.Count, "A").End(xlUp).row

Where ws is a Worksheet object. In the questions example it was implied that the statement was inside a With block

With ws
    lastRowIndex = .Cells(.Rows.Count, "A").End(xlUp).row
End With
  1. ws.Rows.Count returns the total count of rows in the worksheet (1048576 in Excel 2010).
  2. .Cells(.Rows.Count, "A") returns the bottom most cell in column “A” in the worksheet

Then there is the End method. The documentation is ambiguous as to what it does.

Returns a Range object that represents the cell at the end of the region that contains the source range

Particularly it doesn’t define what a “region” is. My understanding is a region is a contiguous range of non-empty cells. So the expected usage is to start from a cell in a region and find the last cell in that region in that direction from the original cell. However there are multiple exceptions for when you don’t use it like that:

  • If the range is multiple cells, it will use the region of rng.cells(1,1).
  • If the range isn’t in a region, or the range is already at the end of the region, then it will travel along the direction until it enters a region and return the first encountered cell in that region.
  • If it encounters the edge of the worksheet it will return the cell on the edge of that worksheet.

So Range.End is not a trivial function.

  1. .row returns the row index of that cell.

More Related Contents:

  1. VBA code takes long time to execute in excel
  2. Excel VBA deleting rows in a for loop misses rows
  3. What is the lifetime of a global variable in excel vba?
  4. Download attachment from Outlook and Open in Excel
  5. What is the most efficient/quickest way to loop through rows in VBA (excel)?
  6. Embed picture in outlook mail body excel vba
  7. How to check for empty array in vba macro [duplicate]
  8. Use autofilter on more than 2 criteria
  9. Compiler Error: User-defined types not defined
  10. Open a workbook using FileDialog and manipulate it in Excel VBA
  11. Vba Excel do vlookup from a closed file
  12. excel hyperlink to nothing
  13. How to add headers to a multicolumn listbox in an Excel userform using VBA
  14. How to send files via HTTP_POST with Excel using VBA?
  15. VBA to open Excel hyperlink does not work when hyperlink generated with a formula
  16. How to Add Date Picker To VBA UserForm
  17. Vba macro to copy row from table if value in table meets condition
  18. Difference between Thisworkbook.name and Activeworkbook.name in VBA
  19. Set a cell value from a function
  20. How to determine if a date falls on the weekend?
  21. What are the differences between using the New keyword and calling CreateObject in Excel VBA?
  22. Excel VBA wildcard search
  23. Run-time error ’91’ (Object variable or With block variable not set)
  24. Calling regasm without administrative rights for COM interop in Excel VBA
  25. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment