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

 

[ER_Neha]

Hello , I am providing you the different way of the above procedure.
But I would Like to inform you that if your previous code already inputs some unwanted data in any cells , then those cells are also considered in the used range.
However, I think you can figure out a way to involve the code I am providing with your already existing code to solve your problem.
Please provide a feedback if this helps.

Sub d()
Dim lastblank As Long, bottomrow As Long

lastblank = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
bottomrow = Sheets(1).Cells(Rows.Count, 1).Row
Range("A" & lastblank & ":" & "A" & bottomrow).Delete

End Sub

 

[ItalianPlatinum]

Hello , I am providing you the different way of the above procedure.
But I would Like to inform you that if your previous code already inputs some unwanted data in any cells , then those cells are also considered in the used range.
However, I think you can figure out a way to involve the code I am providing with your already existing code to solve your problem.
Please provide a feedback if this helps.

Sub d()
Dim lastblank As Long, bottomrow As Long

lastblank = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
bottomrow = Sheets(1).Cells(Rows.Count, 1).Row
Range("A" & lastblank & ":" & "A" & bottomrow).Delete

End Sub

Doesn't seem to be working. if I put random data in say 10 rows down in column C it isn't deleted it

 

[ER_Neha]

Doesn't seem to be working. if I put random data in say 10 rows down in column C it isn't deleted it

It wouldn't delete, because the formula

cells(rows.count,1).endxl(up).row

Or

Used range

Will ensure that all the cells which have the data are covered and only empty cells are left behind.

I would suggest that you could mention the rows that have the unwanted data and purposely delete it.
Or else rectify or Modify your previous code which is giving the unwanted data.

You can also share your previous code here. I am sure some expert will provide a solution to correct the code for you, so that you get rid of the unwanted data

 

[ER_Neha]

H

Doesn't seem to be working. if I put random data in say 10 rows down in column C it isn't deleted it

Hello, Please ignore my previous reply, I tried solving it againg using current region and it worked for my set of assumed data. Please try this once and provide a feedback

Sub delete()
Dim row As Long

row = Range("a:a").CurrentRegion.End(xlDown).row
Range("a" & row + 1, "a" & Cells(Rows.Count, 1).End(xlUp).row - 1).delete

End Sub

 

[ItalianPlatinum]

It wouldn't delete, because the formula

cells(rows.count,1).endxl(up).row

Or

Used range

Will ensure that all the cells which have the data are covered and only empty cells are left behind.

I would suggest that you could mention the rows that have the unwanted data and purposely delete it.
Or else rectify or Modify your previous code which is giving the unwanted data.

You can also share your previous code here. I am sure some expert will provide a solution to correct the code for you, so that you get rid of the unwanted data

Ok I will test it out. I mean i wanted a way to delete unwanted rows not being used so it could still work. ill try it on my actual dataset on monday. thank you

 

[ER_Neha]

Ok I will test it out. I mean i wanted a way to delete unwanted rows not being used so it could still work. ill try it on my actual dataset on monday. thank you

Okay. The last code which I gave should work for you I suppose. If there is or are empty cells between your actual data and unwanted data.
Thank you. Do let us know what is the outcome of the code.

 

[MARK858]

Try using the Find method to determine bottomrow

Sub deleteit()
    Dim lastblank As Long, bottomrow As Long

    lastblank = Workbooks("Compare").Sheets("Periodic").Cells(Rows.Count, 1).End(xlUp).row + 1
    bottomrow = Workbooks("Compare").Sheets("Periodic").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).row
   
    Workbooks("Compare").Sheets("Periodic").Rows(lastblank & ":" & bottomrow).Delete
End Sub

 

[ItalianPlatinum]

Okay. The last code which I gave should work for you I suppose. If there is or are empty cells between your actual data and unwanted data.
Thank you. Do let us know what is the outcome of the code.

it appears to be shifting the cells to the left and not deleting all the empty rows

 

[ItalianPlatinum]

Try using the Find method to determine bottomrow

Sub deleteit()
    Dim lastblank As Long, bottomrow As Long

    lastblank = Workbooks("Compare").Sheets("Periodic").Cells(Rows.Count, 1).End(xlUp).row + 1
    bottomrow = Workbooks("Compare").Sheets("Periodic").Cells.Find("*", , xlValues, , xlByRows, xlPrevious).row
  
    Workbooks("Compare").Sheets("Periodic").Rows(lastblank & ":" & bottomrow).Delete
End Sub

It doesn't seem to be deleting all the unused rows. My data in this examples ends on row 68 (could change) and i am looking to delete row 69 to the end. if i do it manually it works. trying to limit the size of my doc with unnecessary rows its at 85,000kb when i remove empty rows manually it goes to 1,500kb