Delete all rows after last used row

[ItalianPlatinum]

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.

    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

 

[MARK858]

What happens with

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

 

[ItalianPlatinum]

What happens with

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]

What does the error state?

 

[ItalianPlatinum]

What does the error state?

solved it. had wrong sheet. but problem it deleted everything . only want it to delete rows below the last data in Column A.

 

[MARK858]

That code as posted should not delete all your data. This line

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

 .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]

That code as posted should not delete all your data. This line

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

 .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]

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

 

[ItalianPlatinum]

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

Before:

After 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]

No you have changed A1 to B1, do not do this.

After:=.Range("B1")

 

[ItalianPlatinum]

No you have changed A1 to B1, do not do this.

When I was doing A1 it was taking a whole lot of time to run I had to force it closed. my excel ends up having 1millions rows