Modify Code to Find Last Row in Range

Topic

New Reply

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

Reply | Quote

Replies

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 [COLOR=”#008000″]’COLUMNS A TO M[/COLOR]
    s(J) = Cells(Rows.Count, J).End(xlUp).Row
Next J
LastRow = WorksheetFunction.Max(s)
End Sub

HTH,
Maud

Reply | Quote

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

Reply | Quote

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:
45019-VBA-Subscript

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.

:cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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. :angry:

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
[COLOR="#0000FF"]Option Base 0[/COLOR]

Public Sub LastUsedRow()
Dim [COLOR="#0000FF"]s(13) [/COLOR]  As Integer
Dim lastrow As Long
Dim J       As Integer
'--------------------------------
For [COLOR="#0000FF"]J = 1 To 13[/COLOR] '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’ :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Excel,

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

Reply | Quote

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”

Reply | Quote

Maud,

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

45020-VBAArrays

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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 may contain at most 60 elements.
[*]An with a designates a fixed-size array with a number of dimensions equal to the number of elements in the .
[*]The in an or 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 of the that corresponds to the dimension.
[*]If the is present, its provides the lower bound Long data value for the corresponding dimension.
[*]If the 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.

Reply | Quote

Rory,

Thanks that clears it up nicely. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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!

Reply | Quote

Rory,

Could post a link and we can make it a sticky so everyone will have the reference easily visible. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

RG,

Here it is:

https://msdn.microsoft.com/en-us/library/dd361851.aspx

Cheers,

Mitch

Reply | Quote

A.B.,

Thanks for posting. Rory had already replied and I moved it to a Sticky at the top of the forum list. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi Excelnewbie

..to answer your question in post#1:
instead of..
iRow = ws.Cells.Find(What:=”*”, SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

..you can use
iRow = ws.[a:m].Find(What:=”*”, SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

..to restrict your search range to columns A to M

I think it is easier than using a loop and simple to understand.

zeddy

Reply | Quote