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

 

[Peter_SSs]

Hello people,

I have written a code that remove last added row, its very simple it counts from down to up the problem is..

Row 1 and row 2 will not be able to delete I did add "A3" but its still possible to delete row 1 and 2

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

Change your ClearContents part to

If lRow > 2 Then
  Rows(lRow).ClearContents
End If

 

[elmnas]

Change your ClearContents part to

If lRow > 2 Then
  Rows(lRow).ClearContents
End If

doesn't work it doesnt delete any.

hmm

 

[Peter_SSs]

doesn't work it doesnt delete any.

hmm

In that case your original code wouldn't have cleared anything (below row 2) either.
All I've added is to prevent anything being cleared from rows 1 and 2 - which is what I though you were asking.

 

[elmnas]

I did wrote your code in this way:

sub mytest()
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




If lRow > 2 Then
  Rows(lRow).ClearContents
End If


end sub

is this correct?

 

[mathematician]

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.

could someone help me ?

Thank you in advance

What about this?

Sub deleteLast()
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


If LastRow > 2 Then
    Rows(LastRow).ClearContents
End If
End Sub

 

[elmnas]

What about this?

Sub deleteLast()
LastRow = Cells(Rows.Count, 1).End(xlUp).Row



If LastRow > 2 Then
    Rows(LastRow).ClearContents
End If
End Sub

Nothing happens when I run the function.

I dont understand why?..

 

[Peter_SSs]

When I answered before, I didn't actually test your code because you indicated (or at least that is what I thought) that it was working as expected other than it eventually deleted rows 2 and/or 1. I made a suggestion to stop it doing that.

Now that I look more closely at your code (with my addition) it will never delete anything unless rows 1 and 2 are already empty.
The reason is that you are starting at A3 and searching backwards (up). No doubt that search finds something in row 2 (or 1) and my code for prevention of deletion in those rows means that nothing happens.

Change the A3 back to A1 (& still search xlPrevious) & see how it goes.

 

[elmnas]

When I answered before, I didn't actually test your code because you indicated (or at least that is what I thought) that it was working as expected other than it eventually deleted rows 2 and/or 1. I made a suggestion to stop it doing that.

Now that I look more closely at your code (with my addition) it will never delete anything unless rows 1 and 2 are already empty.
The reason is that you are starting at A3 and searching backwards (up). No doubt that search finds something in row 2 (or 1) and my code for prevention of deletion in those rows means that nothing happens.

Change the A3 back to A1 (& still search xlPrevious) & see how it goes.

It seems to work now

ColumnA = Cells(Rows.Count, 1).End(xlUp).Row
    If ColumnA > 2 Then
        Rows(ColumnA).EntireRow.ClearContents
   End If

the issue is there can be content in any column not just column 1 I believe the column range 1-8

do you know how I can set a range of columns ?

thank you in advance

 

[Peter_SSs]

Can we just search in all columns?

Sub mytest2()
  Dim lRow As Long
      
  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 Sub