Results 1 to 28 of 28

Find last row (or column) - why not just use specialcells?

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    11-15-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2003
    Posts
    538

    Find last row (or column) - why not just use specialcells?

    I have heavily studied "how to determine the last row." There are several ways. Chip even has a page on it. What I don't get is, why the complexity? I have been unable to find a flaw in this:
    activesheet.usedrange  ' required. Picked this up from Walkenbach's utils 20 years ago :)
    L = activesheet.cells.specialcells(xlCellTypeLastCell).row  ' or .column
    I'll give what I believe is the answer, which you experts can respond to and ignore the rest below. Is the issue that when people want "last row" then they usually want the last row with cell contents, as opposed to where Control-end lands?

    Everything below considers alternative methods of determining last row or column; not required for an expert to answer.

    Some (maybe including Bob Umlas?) say the "best" way is to search for "*" using xlPrevious. Is that really necessary? Other methods involve End(xlUp) but that's column specific (and they can suffer if the very bottom row in a sheet is used). Chip says that the special cell gives the right row, but potentially wrong column. I've lived long enough to know that when I doubt Chip I always turn out to be wrong, but I've really tried and haven't gotten the incorrect column as he seems to suggest.

    FYI, sht.UsedRange.Rows.Count is tempting but errs when row 1 is not used. A cute solution is
    sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
    which solves that problem but it seems awfully wordy. Another suggested solution is
    sht.UsedRange.Rows.Count + sht.UsedRange.Rows(1).Row - 1

    So why not just sht.cells.specialcells(xlCellTypeLastCell).row ? And .column ? Is there a circumstance that breaks it? And wouldn't it be the most efficient, most "built in" approach? Remember, I'm issuing "activesheet.usedrange" first.

    A few pages about it -
    Chip: http://www.cpearson.com/excel/LastCell.aspx
    Several methods (read the comments too): http://www.thespreadsheetguru.com/bl...lumn-using-vba
    Much detailed analysis: https://www.reddit.com/r/excel/comme...ow_in_a_sheet/
    Last edited by Oppressed1; 09-22-2016 at 05:12 AM.
    Chip, if you can read this, you're STILL making the world a better place with your immeasurably valuable gifts. Every week, you continue to help me (and untold numbers of others) tremendously. Practically no alternative outlets can match your effectiveness, even over the vast number of topics you demystify and empower usage of. You were, and still are, amazing.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. what are specialcells?
    By ammartino44 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-01-2015, 08:35 PM
  2. [SOLVED] How to find the ROW of the blank cells with .SpecialCells(xlCellTypeBlanks)
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 07-08-2014, 01:20 PM
  3. Replies: 3
    Last Post: 05-28-2014, 12:01 PM
  4. Using SpecialCells for multi-column same-row copy/pasting
    By evilgrinners in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-15-2013, 12:06 PM
  5. [SOLVED] SpecialCells
    By mike in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-07-2006, 08:55 PM
  6. [SOLVED] SpecialCells
    By Peter in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-12-2006, 09:10 AM
  7. activecell.specialcells(xlCellTypeVisible) returns column referenc
    By Noel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-04-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1