Select last row and clears content

Question

I have the following sheet in which user input data.

https://dl.dropboxusercontent.com/u/83126653/user%20form%201.png

Now I am using this code to delete the last row

Sub DeleteLastRow()

    Sheets("User Form Engine data 1 Entry").UsedRange. _
    Range("A2:K2").End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).ClearContents  

End Sub

I would like to clear all the last row if even one cell from the row has something in it. need help. Thanks in advance

find last row like here: [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) and then use `Sheets("User Form Engine data 1 Entry").Rows(lastrow).ClearContents`. And also this may be interesting: [How to avoid using Select/Active statements](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Dmitry Pavliv, Apr 17 '14 at 08:15
but I cannot understand what to write and stuff since I don't know how to code and stuff. can you help me please? — Nicholas Agius, Apr 17 '14 at 08:31
what exactly you can't understand? I gave you link to the post where in details described how to determine last row, I also said you what to do next? Do you have any _specific_ qestuoin? I don't see any probmlems wuth implementation — Dmitry Pavliv, Apr 17 '14 at 09:09

score 2 · Accepted Answer · edited May 23 '17 at 12:24

Try using Find to find the last row. It is more robust than the xlDown and xlUp approaches (see Return a range from A1 to the true last used cell)

Last row in columns A:K to be deleted

Sub KillLast()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("User Form Engine data 1 Entry")
Set rng1 = ws.Range("A:K").Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then rng1.EntireRow.Delete
End Sub

Last row in worksheet to be deleted

Sub KillLast()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("User Form Engine data 1 Entry")
Set rng1 = ws.UsedRange.Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then rng1.EntireRow.Delete
End Sub

Select last row and clears content

1 Answers1