VBA to Delete Unused Rows

[maria90]

I have a listobject which can expand in size; sometimes I add new data underneath it and sometimes I delete rows.

What is the best way to delete unused data underneath the listobject? Is it something like "delete all rows from 65553 up to listobject"? How do I do that? Will I have to loop? Currently, the table's range is from A:H and it ends in row 5605.

Thanks for your help

Maria

 

[maria90]

I forgot to mention that I want to delete all rows, even the special ones, as
even though my listobject ends in row 5605, the last row and cell is G5800.

 

[Fishboy]

I forgot to mention that I want to delete all rows, even the special ones, as
even though my listobject ends in row 5605, the last row and cell is G5800.

Hi maria90,

If we are able to define a last row based on column A then you could try the following in a COPY of your workbook:

Sub DeleteUnused()
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Range("A" & LastRow, "A65553").EntireRow.Delete
End Sub

 

[maria90]

Hello Fishboy

Unfortunately, this code doesn't work because when I press CTRL + SHIFT + END the cell is in G5800.
What causes the last cell to be in that position, even though it's empty?

 

[Fishboy]

Hello Fishboy

Unfortunately, this code doesn't work because when I press CTRL + SHIFT + END the cell is in G5800.
What causes the last cell to be in that position, even though it's empty?

Hmm, unfortunately that is hard to tell without seeing the workbook. Are you able to share a copy of it with us? If so, you would need to upload a copy of it to Drop Box, One Drive, Google Drive or something similar, then share the link to it here on the forums.

 

[steve the fish]

Try using:

Application.ActiveSheet.UsedRange
ThisWorkbook.Save

 

[maria90]

Application.ActiveSheet.UsedRange

did the job!

Thanks so much!

Maria