'My excel home page is  http://www.mvps.org/dmcritchie/excel/excel.htm
'This code will create a copy of the current sheet, and retain only the rows
'that have a value in column A.

Sub a1only()
  'David McRitchie -- retain only a1 valued rows in new sheet
  'Create a copy of current sheet,  just before current sheet
  ActiveSheet.Copy Before:=ActiveSheet

  Application.ScreenUpdating = False
  On Error Resume Next
  Dim ir as long, mrows as long, lastcell as range
  Set lastcell = cells.SpecialCells(xlLastCell)
  mrows = lastcell.Row
  'Note rows are deleted from the bottom going up
  For ir = mrows To 1 Step -1
     If Len(Trim(Range("a" & ir).Value)) = 0 Then
        Rows(ir).Delete Shift:=xlUp
     End If
  Next
  Application.ScreenUpdating = True
End Sub

'Also note the following technique, to delete empty rows on same page w/o copying
'UsedRange is same as Range except limited to the used range (before the lastcell)
Sub test222()
     'Sheets("SomeSheet").UsedRange.SpecialCells(xlBlanks).EntireRow.Delete
     ActiveSheet.UsedRange.SpecialCells(xlBlanks).EntireRow.Delete
End Sub