Delete Last Row

[trsisko]

I currently use the following code to move up one question in a spreadsheet that I'm doing; Problem is that it actually keeps the last answer. I actually want to delete the last Excel Row when the Prev Button is pressed. How Can I delete the last row on an excel worksheet?

Private Sub CmdPrevQuestion_Click()
If m_QID = 1 Then
m_QID = 1
CmdPrevQuestion.Enabled = False
Else
m_QID = QID_Prev(m_Question)
 
Call Question_Load
 
CmdPrevQuestion.Enabled = True
End If
 
End Sub
 
'This will return to Last question
Function QID_Prev(Question As String) As Integer
QID_Prev = 0
On Error Resume Next
QID_Prev = Application.WorksheetFunction.Match(Question, shOutput.Range("$B$1:$B$5000"), 0) - 1
If QID_Prev = 0 Then QID_Prev = Application.CountA(shOutput.Columns(1))
QID_Prev = CInt(shOutput.Range("A" & QID_Prev).Value)
End Function

 

[Mike Blackman]

Hi,

To define lastrow use;

Dim LastRow as long

LastRow = Range("A" & Rows.Count).end(xlup).Row

Add that at the top and this will delete the row;

Rows(LastRow).delete

 

[trsisko]

Thanks. Works great

 

[trsisko]

This works great. Though I have two issues with it. I need it to always work on the "output" sheet and secondly I need to switch off Screen updating?

 

[Mike Blackman]

Hi,

As a stand alone this;

Sub Delete_LastRow ()

Application.Screenupdating = False

Dim LastRow as long

LastRow = Sheets("output").Range("A" & Rows.Count).end(xlup).Row

Sheets("output").Rows(LastRow).delete

Application.Screenupdating = True

End Sub

 

[Peter_SSs]

The suggested code could be shortened to:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Delete_LastRow()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Sheets("output").Range("A" & Rows.Count).End(xlUp).EntireRow.Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[trsisko]

I set it as a function and it works a treat. Thanks.