I don’t know if you have ever had an excel file that was way larger then it should be based on the content to had in it. Well i deal with this quite a bit and it’s annoying to say the least but i have a little code that will clear it right up.
Sub DeleteBlankRows() Dim Rw As Long, RwCnt As Long, Rng As Range Application.ScreenUpdating = False Application.Calculation = xlCalculationManual On Error Goto Exits: If Selection.Rows.Count > 1 Then Set Rng = Selection Else Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row())) End If RwCnt = 0 For Rw = Rng.Rows.Count To 1 Step -1 If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then Rng.Rows(Rw).EntireRow.Delete RwCnt = RwCnt + 1 End If Next Rw Exits: Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub
Using this script allows you to delete any blank rows from a spreed sheet and could bring down your file size by a great measure.
I also used this code as an Addin, the deleteblankrows_newer is for office newer than 2003 and deleteblankrows_older is for office 2003 and older.
I hope this helps you out my fellow code monkeys and please comment and let me know how you like it!
P.S. if you don’t know how to add a macro to excel comment below and i will make a post about it.