Lastrow
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
|