Find Last Row in Sheet

[Gregfox]

Hi, I’m using Excel 2007, and wish to find the last row in data sheet “Main”, and put text “END” in column A of that last cell. The complication is not all columns always have data.
I’ve tried;
Sub LastRowTest ()
LastRowColA = Range("A65536").End(xlUp).Row<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
ActiveCell.FormulaR1C1 = "text"<o></o>
End sub<o></o>
======================================================<o></o>
And I also tried;<o></o>
Sub LastRowTest ()
Dim ws As Worksheet<o></o>
Set ws = Worksheets("Main")<o></o>
<o></o>
iRow = ws.Cells(Rows.Count, 1) _<o></o>
.End(xlUp).Offset(1, 0).Row<o></o>
'ActiveSheet.Paste 'ActiveSheet.PasteActiveCell.FormulaR1C1 = "text"<o></o>
End sub<o></o>
<o></o>
NO LUCK… Anyone have an answer?
Thanks!

 

[VoG]

Try

Sub Test2()
Dim LR As Long
With Sheets("Main")
    LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    .Range("A" & LR).Value = "END"
End With
End Sub

 

[jeffreybrown]

How about...

Sub EnterEndLR()
    Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row + 1
    Dim ws As Worksheet: Set ws = Worksheets("Main")
    ws.Range("A" & LR).Value = "End"
End Sub

 

[Gregfox]

Thank you both, both sets of code work great!

 

[Gregfox]

Thank you for the help, but I’m afraid I need a bit more. I thought I could work this out, but am having trouble.
I want to Cut a Row [A4] from a datasheet [MAIN], find the last Empty Row, and Paste it there. Then go back to the top of the file and Cut Row [A5], find the last Empty Row, and INSERT it there {in front of the line that was just put there}, and so on until the macro finds the word END in column A.
Many Thanks.

 

[VoG]

Maybe like this

Sub Test3()
Dim Found As Range
With Sheets("Main")
    Set Found = .Columns("A").Find(What:="END", LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        .Rows("4:" & Found.Row - 1).Cut Destination:=Found.Offset(1)
    End If
End With
End Sub

 

[Gregfox]

This code ran really FAST, but instead of pasting the line in front, it pasted the line after.
What I wanted to happen was as below…
Line A4
Line A5
Line A6
Become
Line A6
Line A5
Line A4
Thank you again!

 

[VoG]

Then perhaps

Sub Test3()
Dim Found As Range, i As Integer
With Sheets("Main")
    Set Found = .Columns("A").Find(What:="END", LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        For i = Found.Row - 1 To 4 Step -1
            .Rows(i).Cut Destination:=Found.Offset(1)
        Next i
    End If
End With
End Sub

 

[Gregfox]

Almost there! This code while much slower than the last, does the right thing except, instead of adding the line at the end, it over writes the last line as below…
Should be …
Line A6
Line A5
Line A4<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
But is<o></o>
Line A6<o></o>
Than<o></o>
Line A5<o></o>
In other words it is doing the right thing but it is replacing the line, instead of adding.<o></o>
I tried changing the offset, but failed.<o></o>
THANKS AGAIN!

 

[VoG]

Try

Sub Test3()
Dim Found As Range, i As Integer
Application.ScreenUpdating = False
With Sheets("Main")
    Set Found = .Columns("A").Find(What:="END", LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        For i = Found.Row - 1 To 4 Step -1
            .Rows(i).Cut Destination:=.Range("A" & Rows.Count).End(xlUp).Offset(1)
        Next i
    End If
End With
Application.ScreenUpdating = True
End Sub