Saturday, October 16, 2010

Delete Empty Rows in Excel Sheet Using Macro


8:34 PM |

Do you know How to Delete empty rows in Excel sheet using a simple Macro. I have also suggest one simple way to remove empty rows from your excel sheet.



Steps To delete empty rows between data using Sorting:

1. Select all columns containing data.
2. Click the Sort icon (either Ascending or Descending).

Steps To delete empty rows between data using Macro:
  1. Open a Microsoft Excel Workbook which contains bland data within it.
  2. Press the ALT+ F11 Key from keyboard to bring up the VBA editor (Double click on your excel spread sheet from left side , which contains blank data).
  3. Paste the below given code and press F5 Key from keyboard.

' Sub Purpose: Delect all blank ROWS within the active cell's

' Used Range

'

Public Sub DeleteBlankRows()

Dim dbMaxRow As Double, dbMinRow As Double, i As Double

Dim dbMaxCol As Double

Dim rng As Range



On Error Resume Next



'only look in used area of the worksheet where active cell is

Set rng = Selection.Parent.UsedRange



'calculate area to be searched for blank rows

dbMaxRow = rng.Rows.Count '# of rows in used area

dbMinRow = rng.Cells(1, 1).Row '1st row in used area

dbMaxCol = rng.EntireColumn.Count '# of columns in used area



For i = dbMaxRow To dbMinRow Step -1

If IsError(rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _

SpecialCells(xlCellTypeBlanks).Count) Then

Else

If rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _

SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then

rng.Cells(1, 1).Offset(i - 1, 0).EntireRow.Delete

End If

End If

Next i

Set rng = Nothing

End Sub


Although I agree that AutoFilter works great for some worksheets, a large worksheet will bog down using this method. If you need to maintain the order of the rows, insert a new column, fill with numbers, sort by desired column, delete blank rows, sort by number column, delete number column.

Note: This not work when cell is containg untype characters, like NULL or formula ="" or something that similar but it is not really enpty cell. Very oftet in occure when copy from NULL from access by CTR + C and CTR + V. Export table feature work correctly and instead of NULL return really empty cell.

Leave A Comment