How to delete last used row?

[elmnas]

Hello people,

I have written a code that remove last added row, its very simple it counts from down to up my issue is I want the code not be able to delete row 1 and 2.

I did wrote "A3"
but its still possible.

Dim lRow As Long
Dim lCol As Long
    
    lRow = Cells.Find(What:="*", _
                    After:=Range("A3"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    
    Rows(lRow).ClearContents

could someone help me ?

Thank you in advance

 

[elmnas]

Thank you Peter_SSs


this code work as a charm!

a last tweak...


I trying to remove the last added row for all the sheets.
But I don't get it work

Could you help me out?

I made this code

Sub DelSheeSeG()


 Dim ws As Worksheet


    For Each ws In ThisWorkbook.Sheets


ActiveWorkbook.Sheets("All_Segments").Activate
 Dim lRow As Long
      
  lRow = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, SearchFormat:=False).Row
  
  If lRow > 1 Then
    Rows(lRow).ClearContents
  End If
Next ws


End Sub

what is wrong?..

 

[Peter_SSs]

I didn't test this but try

Sub mytest3()
  Dim lRow As Long
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    With ws
      lRow = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, SearchFormat:=False).Row
      
      If lRow > 2 Then
        .Rows(lRow).ClearContents
      End If
    End With
  Next ws
End Sub

 

[Peter_SSs]

This would be safer in case you have a worksheet with nothing on it.

Sub mytest3()
  Dim lRow As Long
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    With ws
      lRow = 0
      On Error Resume Next
      lRow = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, SearchFormat:=False).Row
      On Error GoTo 0
      
      If lRow > 2 Then
        .Rows(lRow).ClearContents
      End If
    End With
  Next ws
End Sub

 

[elmnas]

Thank you Peter,

its solved!

 

[Peter_SSs]

Great!