Lastrow
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? |
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 |
Lastrow
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? |
All times are GMT +1. The time now is 05:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com