Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Modify Code to Find Last Row in Range

    Hi Experts,

    I am using the code below to find the last row. I am having a problem and would like to modify it so that the code is restricted to search only columns A to M. I have data in columns beyond M and and that creating problems finding the last row. Dr. Google hasn't provided any help. I am using a header.

    Private Sub CmdEnterData_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Dim wb As Workbook

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")

    iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    Last edited by Excelnewbie; 2016-07-18 at 19:12.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    excel,

    Consider the following alternative code to find the last row in a range:

    Code:
    Public Sub LastUsedRow()
    Dim s(13) As Variant
    '--------------------------------
    For J = 1 To 13 'COLUMNS A TO M
        s(J) = Cells(Rows.Count, J).End(xlUp).Row
    Next J
    LastRow = WorksheetFunction.Max(s)
    End Sub
    HTH,
    Maud

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    As a function

    Code:
    Public Sub Caller()
    x = LastURow(1, 13)
    MsgBox x
    End Sub
    
    
    Public Function LastURow(startcol As Integer, stopcol As Integer) As Integer
    Dim s() As Variant
    '--------------------------------
    For J = startcol To stopcol
        ReDim Preserve s(J)
        s(J) = Cells(Rows.Count, J).End(xlUp).Row
    Next J
    LastURow = WorksheetFunction.Max(s)
    End Function

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Maud,

    I just ran across something that has me sort of baffled.

    In your post #2, I said well that shouldn't work unless there is an Option Base 1 command that isn't shown. But then I said, Maud wouldn't leave an important thing like that out so I thought I'd test it.

    Well it seems that the Option Base command has been removed, more exactly no longer has any effect as it doesn't cause an error if present.

    I tried your code with both Option Base 1 & Option Base 0 with out affecting the outcome of the code.

    So I thought, is it because of the Variant data type? So I changed it to an integer and low and behold still not errors with either Option Base, e.g. at Option Base 0 a 13 position array should be 0-12 and referencing position 13 SHOULD cause an subscript out of range error. It just ain't so.

    You can still get a zero based array but you have to do it in the Dim statement, e.g. Dim s(0 to 12) as Integer. Which will generate the error:
    VBA Subscript.PNG

    I did find one vague reference to Option Base being removed from the language when they went to .net but that was it as far as my Googling went.

    Can anyone else shed any light on this?

    BTW: all testing done with Excel 2010.

    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Many ways to do this.

    Function without an array

    Code:
    Public Sub Caller()
    x = LastURow(1, 13)
    MsgBox x
    End Sub
    
    
    Public Function LastURow(startcol As Integer, stopcol As Integer) As Integer
    '--------------------------------
    For J = startcol To stopcol
        x = Cells(Rows.Count, J).End(xlUp).Row
        If x > LastRow Then LastRow = x
    Next J
    LastURow = LastRow
    End Function

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    RG,

    I look at it this way

    Dim s(13) As Variant means that the array holds 13 values whether For I= 1 to 13 OR For I= 0 to 12 (base 0 or 1)

    Code:
    Public Sub LastUsedRow()
    Dim s(13) As Variant
    '--------------------------------
    For J = 0 To 12 'COLUMNS A TO M
        s(J) = Cells(Rows.Count, J + 1).End(xlUp).Row
    Next J
    LastRow = WorksheetFunction.Max(s)
    End Sub
    Last edited by Maudibe; 2016-07-18 at 21:10.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    148
    Thanks
    67
    Thanked 0 Times in 0 Posts

    Encounter and Error

    Maud,

    I ran into a problem. I'll show more data.

    The error is here: .Cells(iRow, 1).Value = Me.TbxReference.Value


    Private Sub CmdEnterData_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim J As Long
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Sheet1")
    Dim lastrow As Long
    Dim s(13) As Variant


    For J = 1 To 13 'COLUMNS A TO M
    s(J) = Cells(Rows.Count, J).End(xlUp).Row
    Next J
    lastrow = WorksheetFunction.Max(s)

    With ws
    .Cells(iRow, 1).Value = Me.TbxReference.Value
    .Cells(iRow, 2).Value = Me.TbxQType.Value
    .Cells(iRow, 3).Value = Me.TbxCategory.Value
    .Cells(iRow, 4).Value = Me.TbxDifficulty
    .Cells(iRow, 5).Value = Me.TbxAnswer
    .Cells(iRow, 6).Value = Me.TbxQuestion
    .Cells(iRow, 7).Value = Me.TbxMCA
    .Cells(iRow, 8).Value = Me.TbxMCB
    .Cells(iRow, 9).Value = Me.TbxMCC
    .Cells(iRow, 10).Value = Me.TbxMCD
    .Cells(iRow, 11).Value = Me.TbxSource
    .Cells(iRow, 12).Value = Me.TbxMoreInfo
    .Cells(iRow, 13).Value = Me.TbxAddLink
    End With

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Excel,

    In your code, iRow evaluates to 0 so, Cells(iRow,1).value will error

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Quote Originally Posted by Maudibe View Post
    RG,

    I look at it this way

    Dim s(13) As Variant means that the array holds 13 values whether For I= 1 to 13 OR For I= 0 to 12 (base 0 or 1)
    Maud,

    But the OLD Programmer that I am says it SHOULD NOT work that way! These are the things that Buffer Overrun Exploits are made of.

    When either loop counter will work regardless of the Option Base value there are shenanigans afoot!

    This should cause an Subscript out of range error and it doesn't!
    Code:
    Option Explicit
    Option Base 0
    
    Public Sub LastUsedRow()
    Dim s(13)   As Integer
    Dim lastrow As Long
    Dim J       As Integer
    '--------------------------------
    For J = 1 To 13 'COLUMNS A TO M
        s(J) = Cells(Rows.Count, J).End(xlUp).Row
    Next J
    lastrow = WorksheetFunction.Max(s)
    
    MsgBox "Last Row: " & Format(lastrow, "####"), _
           vbInformation + vbOKOnly, _
           "Testing Last Row in A-M"
    End Sub
    I'm just sayin'
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 644 Times in 588 Posts
    Whereas, For I=2 to 14 does cause a "subscript out of range" error.

    Perhaps better explained that Dim s(13) sets the upper bound of the array and in Option base 0, s(0) is nothing while in Option base 1, s(0) produces "subscript out of range"
    Last edited by Maudibe; 2016-07-18 at 21:32.

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Maud,

    I just found this in "Excel 2010 Power Programming with VBA", By John Walkenbach

    VBAArrays.PNG

    That sure explains a lot...Who knew? Not me that's for sure!

    That will sure change how I declare arrays in VBA going forward.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Per the VBA Language specification for Array Dimension and Bounds (sect 5.2.3.1.3):
    array-dim = “(“ [bounds-list] “)”
    bounds-list = dim-spec *(“,” dim-spec)
    dim-spec = [lower-bound] upper-bound
    lower-bound = constant-expression “to”
    upper-bound = constant-expression

    Static Semantics
    • An <array-dim that does not have a <bounds-list designates a resizable array.
    • A <bounds-list> may contain at most 60 <dim-spec> elements.
    • An <array-dim> with a <bounds-list> designates a fixed-size array with a number of dimensions equal to the number of <dim-spec> elements in the <bounds-list>.
    • The <constant-expression> in an <upper-bound> or <lower-bound> must evaluate to a data value that is let-coercible to the declared type Long.
    • The upper bound of a dimension is specified by the Long data value of the <upper-bound> of the <dim-spec> that corresponds to the dimension.
    • If the <lower-bound> is present, its <constant-expression> provides the lower bound Long data value for the corresponding dimension.
    • If the <lower- bound> is not present the lower bound for the corresponding dimension is the default lower bound for the containing module as specified in Section 5.2.1.2.


    For each dimension, the lower bound value must be less than or equal to the upper bound value.
    Note that the lower bound is optional, the upper is not.

    Hence, any array declaration that only provides one number for a dimension, is providing the upper boundary, as Maud said, not the number of elements, which will be dependent on the Option Base.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. The Following User Says Thank You to rory For This Useful Post:

    RetiredGeek (2016-07-19)

  14. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Rory,

    Thanks that clears it up nicely.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  15. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It's always worth looking in the Language spec document. It takes a little while to get used to some of its syntax, but it's a mine of useful information!
    Regards,
    Rory

    Microsoft MVP - Excel

  16. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Rory,

    Could post a link and we can make it a sticky so everyone will have the reference easily visible.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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