25 November, 2008

Delete empty rows in Excel

When I get data from the screen view in Salesforce.com, the results have an empty row between each row of data. With a little VBA help to create a macro, it's easy to clean it up. Here's how to do this:
  1. Go to Tools >> Macros >> Visual Basic Editor OR press Alt-F11.
  2. The Project Explorer should be open in the upper-left corner. If not press Ctrl-R.
  3. In the Project Explorer, double-click the worksheet name to add the macro only to a single worksheet. Double-click ThisWorkbook to have it available to all worksheets.
  4. Paste the code (option 1 or option 2) into the worksheet.
  5. Go to View >> Microsoft Excel OR press Alt-F11.
  6. Select column A.
  7. Go to Tools >> Macros >> Macros OR press Alt-F8.
  8. Highlight the DeleteRows macro and click Run.
  9. Wait as it process through each row. To make it faster, only highlight the rows you want checked.
OPTION 1
Sub DeleteRows()
RowCount = Selection.Rows.Count
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For i = 1 To RowCount
If ActiveCell.Value = "" Then
Selection.EntireRow.Delete
Else
ActiveCell.Offset(1, 0).Select
End If
Next i
Application.ScreenUpdating = True
End Sub
OPTION 2
Sub DeleteRows()
Dim theRows As Range
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Selection.SpecialCells(xlCellTypeBlanks).Select
For Each Rw In Selection.Rows
If WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
Selection.EntireRow.Delete
End If
Next theRows
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
Option 1 seems a little easier, while option 2 is a little faster. Option 1 checks for row value (= ""), while option 2 counts the number of empty cells in to see if it is equal to 0. Option 2 also turns off calculations, which is where the real speed is gained.

That's it, now you have a worksheet without empty rows.

No comments: