Empty cell deletion at bottom of worksheet

**DarCady** · 09-24-2009, 01:22 PM

On some of my worksheets, the slider bar on the right has become a small square rather than a long rectangle. This causes this slightest shift downward to jump through large amounts of rows. Excel seems to think there must be data down there I need. How do I get rid of empty cells at the bottom of a worksheet so that this no longer happens?

**shg** · 09-24-2009, 01:27 PM

Select the first empty row below your data, do Ctrl+Shift+Down Arrow, Edit > Delete.

**JP Romano** · 09-24-2009, 03:25 PM

I use the code below to do it in multi-sheet workbooks. If you have Excel 2003 or earlier, you'll need to change "a100000" to "A65536"

Sub deleteblankrows()
   
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
    sh.Activate
    Range("a100000").End(xlUp).Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete
Next sh
     
End Sub