[Bishop]

I'm familiar with this code to find the last row:

LastRow = .Range("A" & Rows.Count).End(xlUp).Row

But what if I need LastRow to be at a certain value? If I know column A is
going to have data and I want to assign LastRow the first row that has a
value of 0 how would I do that? What if I need LastRow to be the row 2 rows
above the first row that has a value of 0?

[Dave Peterson]

I would search for the 0 and then use that (subtract 2 or whatever):

Option Explicit
Sub testme()

Dim myRow As Long
Dim FoundCell As Range
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

myRow = 0 'can't happen in real life!
With wks
With .Range("a1").EntireColumn
Set FoundCell = .Cells.Find(what:=0, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With
End With

If FoundCell Is Nothing Then
MsgBox "not found!"
Else
If FoundCell.Row 2 Then
myRow = FoundCell.Row - 2
End If
End If

MsgBox myRow

End Sub

The .find looks for 0 after the last cell in column A and for the Next
occurrence--equivalent of looking at the top to the bottom.

If I wanted to find the last 0 in the range, I'd use:

With .Range("a1").EntireColumn
Set FoundCell = .Cells.Find(what:=0, _
after:=.Cells(1), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
End With

This starts at the first cell and looks for the previous (up the range)
occurrence.

Bishop wrote:

I'm familiar with this code to find the last row:

LastRow = .Range("A" & Rows.Count).End(xlUp).Row

But what if I need LastRow to be at a certain value? If I know column A is
going to have data and I want to assign LastRow the first row that has a
value of 0 how would I do that? What if I need LastRow to be the row 2 rows
above the first row that has a value of 0?

--

Dave Peterson

[Gary''s Student]

LastRow is just the name of a variable that can be assigned any value:

LastRow=1
or
LastRow=5+7

The formula you posted just assigns it to the last filled cell in some
column. To set LastRow to the first cell in column A that has the value of
zero:

Sub marine()
For i = 1 To Rows.Count
If Cells(i, "A").Value = 0 Then
LastRow = i
Exit For
End If
Next
' more code here
End Sub

--
Gary''s Student - gsnu200852


"Bishop" wrote:

I'm familiar with this code to find the last row:

LastRow = .Range("A" & Rows.Count).End(xlUp).Row

But what if I need LastRow to be at a certain value? If I know column A is
going to have data and I want to assign LastRow the first row that has a
value of 0 how would I do that? What if I need LastRow to be the row 2 rows
above the first row that has a value of 0?