To change Columns(4).Rows.Count

[Guest]

Hi guys I would like to know how to sustitute the "Columns(4).Rows.Count"
statement for something more efficient, the Idea is to know in wich cell I
find the last record, instead off read all the column , there are no empty
cells in between, I had to assign Long to "i" because I got an overflow with
byte or integer.

Thanks


Dim i As Long, j As Integer

For j = 1 To 10
For i = 1 To Columns(4).Rows.Count
If Cells(i, 4) = Cells(j, 1) Then
Cells(i, 5) = Range("Especias")(j, 2).Value
End If
Next i
Next j

End Sub

 

[Guest]

You can get the last row for that column with
lr4 = Cells(rows.Count, 4).End(xlUp).Row
And then just plug it into
For i = 1 To lr4
The way you have it would equal 1 To 65356.
With the lr4 it will only go to the last row in that column with data.