How to delete last row in Excel ?

[dilyanyordanov]

Hi guys,
I am creating an automated report every afternoon. It goes into an ERP system and copies few columns which are then pasted into Excel. How do I make (maybe create a vba macro but I am still new to macros) Excel delete the last row of the pasted columns. Note that every day the row will be = row + 1. If i can do this in macros and create a button, I can make the automation to click the button and delete the row before sending it. Any help will be well appreciated.

 

[oldbrewer]

if today the row to be deleted is row 100, tomorrow 101 etc

in cell Z1 enter 100

in a macro put row(cells(1,26).select
Selection.Delete Shift:=xlUp
and cells(1,26)=cells(1,26)+1

 

[MARK858]

Try...

ActiveSheet.Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete

Where column A is the longest column.

 

[dilyanyordanov]

Try...

ActiveSheet.Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete

Where column A is the longest column.

How would this code know where my last row right now is ? Say I am at row 40 today. If I finish my program today and start it, in excel in column A,B,C,D there will be 41 rows + another one...the 42nd which is the total amount of all the previous rows (which I want to delete, every day).Just trying to illustrate it better, so you can be able to help me easier.

 

[MARK858]

Did you actually try the code?
The End(xlUp) looks from the bottom of the sheet until it finds something in column A.

 

[LaWolfe]

Hello:

Using your helpful tip, my code is only deleting the very last row. Can you assist with how to delete the last four dynamic rows please?
Sub CycleCounts()
'
' CycleCounts Macro
'

'
Application.Left = 1516
Application.Top = 9.25
Columns("A:G").Select
Selection.Copy
Sheets("SourceExported_Data").Select
Range("M1").Select
ActiveSheet.Paste
Sheets("Uncounted").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("SourceExported_Data").Select
Sheets("SourceExported_Data").Copy After:=Sheets(1)
Sheets("SourceExported_Data (2)").Select
Sheets("SourceExported_Data (2)").Name = "Uncounted"
ActiveSheet.Cells(Rows.Count, "M").End(xlUp).EntireRow.Delete
Sheets("SourceExported_Data").Select
End Sub

 

[Joe4]

You can add this to your code:

Sub CycleCounts()
'
' CycleCounts Macro
'

'
Application.Left = 1516
Application.Top = 9.25
Columns("A:G").Select
Selection.Copy
Sheets("SourceExported_Data").Select
Range("M1").Select
ActiveSheet.Paste
Sheets("Uncounted").Select
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.Delete
Sheets("SourceExported_Data").Select
Sheets("SourceExported_Data").Copy After:=Sheets(1)
Sheets("SourceExported_Data (2)").Select
Sheets("SourceExported_Data (2)").Name = "Uncounted"
Dim lr as Long
lr = ActiveSheet.Cells(Rows.Count, "M").End(xlUp).Row
If lr > 3 Then Rows(lr-3 & ":" & r).Delete
Sheets("SourceExported_Data").Select
End Sub

 

[MARK858]

The code @Joe4 posted will use less resources (and the test for >3 rows is a good idea) but another option is

ActiveSheet.Cells(Rows.Count, "M").End(xlUp).Offset(-3).Resize(4).EntireRow.Delete

 

[KSSLR]

Try...

ActiveSheet.Cells(Rows.Count, "A").End(xlUp).EntireRow.Delete

Where column A is the longest column.

This helped me in 2024