How do I delete Blank Rows apearing intermittenly amidst rows of data?
I have a worksheet filled with rows of data. My problem is that I wish to delete only those rows that are completely blank, say for esample I have a worksheet having data from A1:Z1 to A16636:Z16636, i.e. there are 16636 rows of data. It is possible that the first column in some of the rows may be blank, but I want only those rows to be deleted where all the cells in columns A to Z are completely blank. I found a code snippet (attached below) which deletes all rows not having data in column "A" but it does not solve m problem.
Please help!!
Please help!!
Sub DeleteBlankRows()
'---------------------------------------------------------------------------------------
' Procedure : BlankRows
' DateTime : 07/06/2008 21:38
' Author : Vikram S. Mathur, FCA
' Purpose :
'---------------------------------------------------------------------------------------
'
Dim lRange As Long
Dim lLoop As Long
On Error GoTo DeleteBlankRows_Error
lRange = InputBox("How many rows?", "Row Count", ActiveCell.SpecialCells(xlLastCell).Row)
If lRange <> 0 Then
ActiveCell.Offset(0, 0).Select
Application.ScreenUpdating = False
For lLoop = 1 To lRange
If Trim(ActiveCell.Value) = "" Then
Selection.EntireRow.Delete
Else: ActiveCell.Offset(1, 0).Select
End If
Application.StatusBar = "Checking Row: " & lLoop & " of " & lRange
Next lLoop
End If
Application.StatusBar = False
Application.ScreenUpdating = True
On Error GoTo 0
DeleteBlankRows_Exit:
Exit Sub
DeleteBlankRows_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DeleteBlankRows of Module basDeleteBlankRows"
Resume DeleteBlankRows_Exit
End Sub
ASKER
Dear MikeTob,
I am looking for VBA Code to handle the solution to this problem, as many a times, this needs to be handled from within code, as there is very little human intervention involved in the aforesaid spreadsheet. It could have worked as a perfectly valid solution in the scenario if the entire spreadsheet was being managed in the human intervention mode, unfortunately, it is not, so it has to be a coded intervetion that is the solution I am looking for.
I am looking for VBA Code to handle the solution to this problem, as many a times, this needs to be handled from within code, as there is very little human intervention involved in the aforesaid spreadsheet. It could have worked as a perfectly valid solution in the scenario if the entire spreadsheet was being managed in the human intervention mode, unfortunately, it is not, so it has to be a coded intervetion that is the solution I am looking for.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot, Miketob, it really did help!!
Perhaps you could insert a column at the begining of the sheet, (i.e a new A column) and fill that column with this formula:
Formula in cell A1:
= B1 & C1 & D1 & E1 & ... & Z1
so this cell will have the concatenation of all values in a row, and then run your same code snippet, so if this first cell is blank, it will be because all the row is empty.
Hope it helps.
Regards