Blank Rows (Excel XP)

Topic

New Reply

I am looking for a way to identify blank rows and delete them in a sheet. I did a search on Blank Rows and did not see anything that looked like what I want to do. I looked at Go To Special and did not see a way to select the blank rows only a row comparison.

Carla

Reply | Quote

Replies

Perhaps you can sort the sheet on the appropriate column(s)? Blanks will always be sorted at the bottom, which has the same effect as deleting them.

Reply | Quote

The sheet gets data from another data source and the data is not in a very usable format. I an trying to create a macro that will clean up and format the sheet after an import. I have identified how to delete the numbers that do not need to be there, how to split a text column of data to leave the numbers I need for a grand total, how to create the formulas, now I am looking for a way to identify what rows are left completly blank in the dataset and delete them. This is just a part of a larger process, so sorting does not seem to work with the multiple columns.

Carla

Reply | Quote

See Hans post 458,048.

(I’m surprised you didn’t find this in your search.)

Reply | Quote

That is exactly what I was looking for. I might not have gone back far enough. Thank you very much.
Carla

Reply | Quote

Try this procedure:

Sub DeleteBlankRows(Optional WorksheetName As Variant)
Dim WS As Worksheet
Dim LastRow As Long
Dim RowNum As Long

If IsMissing(WorksheetName) = True Then
Set WS = ActiveSheet
Else
On Error Resume Next
Set WS = ActiveWorkbook.Worksheets(WorksheetName)
If Err Then
‘ Invalid worksheet name.
Exit Sub
End If
On Error GoTo 0
End If

If Application.WorksheetFunction.CountA(WS.UsedRange.Cells) = 0 Then
‘ Worksheet is blank. Get Out.
Exit Sub
End If

‘ Find the last used row
LastRow = WS.Cells.Find(What:=”*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

‘ Loop bacvkwards
For RowNum = LastRow To 1 Step -1
‘ If there are no non-blank cells…
If Application.WorksheetFunction.CountA(WS.Rows(RowNum)) = 0 Then
‘ Delete the entire row
WS.Range(“A” & RowNum).EntireRow.Delete
End If
Next RowNum
End Sub

Call like this to delete blank rows in the active worksheet:

Call DeleteBlankRows

and like this to delete blank rows in a sheet named Data:

Call DeleteBlankRows(“Data”)

The code is a much simplified version of the code found in Deleting Rows on Chip Pearson’s site.

Reply | Quote

Hans,

Had to leave earlier today so did not have time to test until this afternoon. I used the code you supplied and I was able to complete a 6 step consolidated macro successfully. Thank you again for the code to complete the process.

Carla

Reply | Quote