Delete all rows after last used row

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
279
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
I found this VBA code on this site. but it doesn't work. and was hoping if someone could assist me as to why

I am looking to delete all rows below the last row of data in column A. I noticed my excel creates many unused rows after my other code runs.
VBA Code:
    Dim bottomrow, lastblank As Long

bottomrow = Workbooks("Compare").Sheets("Periodic").UsedRange.rows.count
lastblank = Workbooks("Compare").Sheets("Periodic").Cells(rows.count, 1).End(xlUp).row + 1
  
Range("A" & lastblank & "A" & bottomrow).EntireRow.Delete

1602264331050.png
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Then you have something strange in your cells.
The After parameter in Find is for doing the lookup, basically when using XLPrevious it looks at the cell before A1 which is the last cell on the sheet and then it looks backwards from there, changing it to B1 changes the start point of the search.
What happens with
VBA Code:
Sub LoseThatWeight()

    Dim x As Long, LastRow As Long

    Application.ScreenUpdating = False

    With Sheets("Compare")
        LastRow = .Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).row
        .Rows(LastRow + 1 & ":" & Rows.Count).delete
    End With
   
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
279
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Then you have something strange in your cells.
The After parameter in Find is for doing the lookup, basically when using XLPrevious it looks at the cell before A1 which is the last cell on the sheet and then it looks backwards from there, changing it to B1 changes the start point of the search.
What happens with
VBA Code:
Sub LoseThatWeight()

    Dim x As Long, LastRow As Long

    Application.ScreenUpdating = False

    With Sheets("Compare")
        LastRow = .Columns(1).Find("*", , xlValues, , xlByRows, xlPrevious).row
        .Rows(LastRow + 1 & ":" & Rows.Count).delete
    End With
  
    Application.ScreenUpdating = True
End Sub
Lean, mean and fighting machine now :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,109,545
Messages
5,529,458
Members
409,879
Latest member
Aussie_Excel_Wanna_Be
Top