Results 1 to 5 of 5

Thread: Some doubts on current vba code for defining rows in spreadsheet

  1. #1
    Join Date
    Jan 2011
    Posts
    15

    Some doubts on current vba code for defining rows in spreadsheet

    I got some help here writing some code. I am trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the. Count does, or why the xlUp is not a xlDown. Can someone explain this line for me.

  2. #2
    Join Date
    Jul 2011
    Posts
    623

    Re: Some doubts on current vba code for defining rows in spreadsheet

    In the code, Rows.Count returns the number of rows in the worksheet, or 65536. Therefore, the Cells(Rows.Count,2) refers to the last cell in column 2. Then, the .End(xlUp) causes Excel to scan upwards until a non-empty cell is found. The .Row property returns the row number of that last cell.

  3. #3
    Join Date
    Aug 2011
    Posts
    566

    Re: Some doubts on current vba code for defining rows in spreadsheet

    The rows.count returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this. lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2).

  4. #4
    Join Date
    Jan 2011
    Posts
    15

    Re: Some doubts on current vba code for defining rows in spreadsheet

    Thanks to everyone for your prompt and helpful replies. Your explanations beg the question, why count up from the bottom instead of down from the data itself. Is this to handle the potential for blank cells in that column. If so, then if there were blanks in the bottom rows in this column, you'd get a miscount anyway. Is there some other reason for counting up from the bottom of the sheet.

  5. #5
    Join Date
    Aug 2011
    Posts
    540

    Re: Some doubts on current vba code for defining rows in spreadsheet

    If the data contains embedded blank cells, the End(xlDown) method will not take you to the last cell. It will take you to the cell above the first blank cell. Going from the bottom up will always take you to the last cell with data.

Similar Threads

  1. Replies: 10
    Last Post: 24-02-2012, 02:12 PM
  2. Having some doubts on Excel VBA Code
    By cupid! in forum MS Office Support
    Replies: 4
    Last Post: 23-02-2012, 12:21 PM
  3. Defining and controlling database in SQL
    By GOOL in forum Software Development
    Replies: 5
    Last Post: 25-12-2010, 08:42 AM
  4. Replies: 2
    Last Post: 09-07-2009, 08:57 AM

Tags for this Thread

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
  •  
Page generated in 1,711,755,992.97214 seconds with 17 queries