Delete Unused cells and rows

[guamlenahans]

I am using the macro below:


Sub DeleteBlankRows()
Dim x As Long

With ActiveSheet
For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _
To 1 Step -1

If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete
End If

Next
End With

End Sub


Trying to figure out how to modify it to delete all unused rows after row 110. Eventually want to delete all columns after Z as well on all sheets in the workbook. Any help is appreciated.

 

[kpark91]

Why do you want to delete unused rows/columns?

If you don't want to be shown on your worksheet, deleting won't do anything but hiding them will.

 

[ogo]

Sub DeleteBlankRows()
Dim x As Long
With ActiveSheet
    For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _
    To 1 Step -1
        If x > 110 Then
            If WorksheetFunction.CountA(.Rows(x)) = 0 Then
            ActiveSheet.Rows(x).Delete
            End If
        End If
    Next
End With
End Sub

 

[Maheshp]

Hi

hope this will help
www.excelitems.com/2010/11/shrink-reduce-excel-file-size.html

 

[guamlenahans]

Perfect, thanks!