Delete all rows after last used row

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
278
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
What happens with
VBA Code:
Sub LoseThatWeight()

    Dim x As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

        With Workbooks("Compare").Sheets("Periodic")

            LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With

    
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
278
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
What happens with
VBA Code:
Sub LoseThatWeight()

    Dim x As Long, LastRow As Long, LastCol As Long

    Application.ScreenUpdating = False

        With Workbooks("Compare").Sheets("Periodic")

            LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            LastCol = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(Rows.Count, Columns.Count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete
        End With

   
    Application.ScreenUpdating = True
End Sub
it errors on this

.Range(.Cells(1, LastCol + 1), .Cells(rows.count, Columns.count)).Delete
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What does the error state?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

That code as posted should not delete all your data. This line
VBA Code:
LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
finds the last used cell in all columns.
This line goes from 1 line below the above
Rich (BB code):
 .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete

Please post your workbook on a free file hosting site like www.box.com or www.dropbox.com. Mark the file for sharing and paste the link it provides in the thread.
Make sure that you amend any sensitive data before sharing.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
278
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
That code as posted should not delete all your data. This line
VBA Code:
LastRow = .Cells.Find(What:="*", After:=.Range("A1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
finds the last used cell in all columns.
This line goes from 1 line below the above
Rich (BB code):
 .Range(.Cells(LastRow + 1, 1), .Cells(Rows.Count, Columns.Count)).Delete

Please post your workbook on a free file hosting site like www.box.com or www.dropbox.com. Mark the file for sharing and paste the link it provides in the thread.
Make sure that you amend any sensitive data before sharing.
unfortunately I cannot share it has sensitive user information. is it because my data doesn't start until Row A13 and then could end anywhere. so A1:A12 is blank
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

A1:A12 being blank makes no difference to that code.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
278
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
A1:A12 being blank makes no difference to that code.
Before:
1602427395499.png


After Code:
1602427435077.png


VBA Code:
    Application.ScreenUpdating = False

        With Sheets("Compare")

            LastRow = .Cells.Find(What:="*", After:=.Range("B1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
            LastCol = .Cells.Find(What:="*", After:=.Range("B1"), LookIn:=xlValues, _
                                  LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
            .Range(.Cells(1, LastCol + 1), .Cells(rows.count, Columns.count)).Delete
            .Range(.Cells(LastRow + 1, 1), .Cells(rows.count, Columns.count)).Delete
        End With

    Application.ScreenUpdating = True
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,336
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
No you have changed A1 to B1, do not do this.
Rich (BB code):
After:=.Range("B1")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,410
Messages
5,528,613
Members
409,828
Latest member
99DodgeRam

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top