Delete Unused Rows

[Dazzawm]

I have some files that may only have a used range of 1000 rows for example. But when I scroll down it goes down to the maximum amount of rows. Is there a code that I can run that will delete unused rows as it takes quite some time when doing formulas or a 'find' for example.

 

[Shinod]

I have some files that may only have a used range of 1000 rows for example. But when I scroll down it goes down to the maximum amount of rows. Is there a code that I can run that will delete unused rows as it takes quite some time when doing formulas or a 'find' for example.

Sub Delete_unusedcells()
' Delete_unusedcells Macro
    Cells.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
End Sub

Try this. If not working, share your sheet. I can fix that.

 

[Dazzawm]

Sub Delete_unusedcells()
' Delete_unusedcells Macro
    Cells.Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.Delete Shift:=xlUp
End Sub

Try this. If not working, share your sheet. I can fix that.

Thanks but that didn't work. Unfortunately I cannot share file as it contains sensitive data.

 

[Shinod]

Thanks but that didn't work. Unfortunately I cannot share file as it contains sensitive data.

Create a dummy and share then.

 

[Alex Blakenburg]

I have some files that may only have a used range of 1000 rows for example. But when I scroll down it goes down to the maximum amount of rows. Is there a code that I can run that will delete unused rows as it takes quite some time when doing formulas or a 'find' for example.

What do you mean when you say Used Range ?
Do you mean visually there are only a 1000 rows or do you mean in VBA code similar to Activesheet.usedrange.address is picking up a much larger area than you are expecting ?
Do you have formulas that go right down to the bottom of the sheet but are returning "" and are you happy to delete these rows ?

 

[Alex Blakenburg]

Just to keep things moving, try this on a COPY of your workbook.

Sub DeleteRowsBelowData()

    Dim usedRng As Range
    Dim lastRow As Long, usedRngLastRow
    Dim sht As Worksheet
    
    Set sht = ActiveSheet

    Set usedRng = sht.UsedRange
    usedRngLastRow = usedRng.Rows.Count + usedRng.Cells(1, 1).Row - 1
    
    lastRow = sht.Cells.Find("*", LookIn:=xlValues, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious).Row

    sht.Rows(lastRow + 1).Resize(usedRngLastRow - lastRow).Delete
    
    ' Reset the New Used Range
    Dim ForResetOnly As String
    ForResetOnly = sht.UsedRange.Address

End Sub

 

[Dazzawm]

What do you mean when you say Used Range ?
Do you mean visually there are only a 1000 rows or do you mean in VBA code similar to Activesheet.usedrange.address is picking up a much larger area than you are expecting ?
Do you have formulas that go right down to the bottom of the sheet but are returning "" and are you happy to delete these rows ?

What I mean is I may have data going down to row 10000, but the sheet goes down to maybe 500000 or more. So thousands of empty rows slowing things down.

 

[Alex Blakenburg]

What I mean is I may have data going down to row 10000, but the sheet goes down to maybe 500000 or more. So thousands of empty rows slowing things down.

Did you try the code ?

 

[Dazzawm]

Did you try the code ?

Sorry for the late response. When I run it I get a red cross saying 400?

 

[Dazzawm]

Any ideas why this is happening please?