This page demonstrates how you can use and create a macro that deletes blank rows in excel. In working with this program, you need to define a column that is used to test for blanks. A loop works through the rows for the selected column. When a blank is found it is deleted. You should work backwards from the bottom of the sheet. A new sheet is make in case there is a problem with the macro.
Using the Delete Rows Macro in Generic Macros
The sheet below demonstrates an example of a file that has some blanks. If you want to delete the rows that are blank you can use the generic macro file.
The generic macro file that has the delete blank row macro is available for downloading below. If you have already downloaded the file you do not have to do it again.
To remove blank rows, I created a macro in the generic macros file. So, open the generic macro file. Then press the CNTL, ALT, C sequence to get the colouring menu. On this menu there is a option for deleting blank rows as shown in the screenshot below. Sorry about the crude circle.
After you press the delete rows button, a input box message appears. In this box, you enter the column number to test for deleting rows. This means that if the column you selected has a blank, the row will be deleted. The screenshot below demonstrates how you enter the column number (not the column letter).
After you enter the column number, the rows are deleted and the results are put in a new sheet (in case you do something wrong and to much is deleted). The manner in which the blank rows are deleted is demonstrated in the screenshot below. Notice that sheet1 — the place where the original rows were read in is now sheet1(5) — this is a new sheet with the deleted blank rows. The original sheet is still there.
Technical Details for Writing Your Own Macro
The VBA code below demonstrates how you can do this yourself. You need to find the number of rows and work backward with a for loop. A macro for deleting rows and deleting columns is presented.
.
Sub x_Delete_rows()
Dim col_test As Single
x_find_rows
x_find_cols
On Error GoTo set_zero:
col_test = InputBox("Column Number as Basis to Test for Deletion (Blank or Zero tests that Entire Column is Blank)" _
& Chr(13) & Chr(13) & " Enter a NUMBER not a letter")
GoTo skip:
set_zero:
col_test = 0
skip:
copy_sheet
If col_test = zero Then
start_col = 1
end_col = max_col
Else
start_col = col_test
end_col = col_test
End If
' MsgBox " Maximum Rows " & max_row & " Maximum Columns " & max_col
For delete_row = max_row To 1 Step -1 ' work through rows from end
keep_row = False ' keep rows set to false (meaning delete)
Cells(delete_row, 1).Select
For col = start_col To end_col ' work around cols check if anything there
If WorksheetFunction.IsNumber(Cells(delete_row, col)) = True _
Or WorksheetFunction.IsText(Cells(delete_row, col)) = True Then
keep_row = True
End If
Next col
If keep_row = False Then ' delete row if you havent found anything
Selection.EntireRow.Delete
End If
Next delete_row
End Sub
Sub x_Delete_cols()
'
x_find_rows ' find rows and cols from this function
x_find_cols
' MsgBox " Maximum Rows " & max_row & " Maximum Columns " & max_col
For delete_col = max_col To 1 Step -1 ' key is to work backwards
keep_col = False
Cells(1, delete_col).Select
For row = 1 To max_row
If WorksheetFunction.IsNumber(Cells(row, delete_col)) = True _
Or WorksheetFunction.IsText(Cells(row, delete_col)) = True Then
keep_col = True
End If
Next row
If keep_col = False Then ' After finding blank colum, delete
Selection.EntireColumn.Delete
End If
Next delete_col
End Sub