#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    4
    Rep Power
    0

    Unhappy EndRow Function in Excel


    I am trying to define EndRow in an Excel recordset in some code I'm writing.

    My definition of EndRow is currently:
    iEndRow = Range("A:A").End(xlDown).Row

    The excel recordset contains multiple blank rows (distributed through the dataset) and so iEndRow does not describe the last row containing data. It describes the last row of data before the first of the blank rows.

    Does anybody know how I can define the EndRow of my dataset that contains blank rows.

    Please Help.
    Suzanne
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    Melbourne, Australia
    Posts
    212
    Rep Power
    12
    Do the reverse, instead of going down, go up from the bottom of the worksheet. See code below

    Code:
    Dim Lastrn 'declare variable to store last range
    Set Lastrn = Worksheets("Sheet1").Range("a65536").End(xlUp)
    
    'now get the row number, add 1 to it because Lastrn is the last 
    'row with data in it, this gives the first blank cell after the data
    
    Dim LastRowNumber
    LastRowNumber = Lastrn.Row + 1
    Hope this helps
    How can I soar like an eagle when
    I'm flying with turkey's?

IMN logo majestic logo threadwatch logo seochat tools logo