PDA

View Full Version : Search Range and Delete any Blank Rows



Panda
11-26-2010, 06:10 AM
Hi There,

I have a table with some data in but also blank rows, although I have the code to delete a particular cell, can it be modified to search through a range and delete a row if it is blank and then shift the rows up 1?


Here is the code Ive used to delete one particular cell;



Sub DeleteBlankCells()


If Range("a2").Value = NullString Then
Range("A2").Delete Shift:=xlUp
End If
End Sub


Can anybody help me?

Thanks in advance

Phil

Bob Phillips
11-26-2010, 06:57 AM
Sub DeleteBlankCells()
Dim Lastrow As Long
Dim i As Long

Lastrow = Cells(Rows.Count,"A").End(xlUp).row
For i = Lastrow To 1 Step -1

If .Cells(i, "A").Value2 = "" Then

.Rows(i).Delete
End If
Next i
End Sub

Panda
11-27-2010, 09:05 AM
Sub DeleteBlankCells()
Dim Lastrow As Long
Dim i As Long

Lastrow = Cells(Rows.Count,"A").End(xlUp).row
For i = Lastrow To 1 Step -1

If .Cells(i, "A").Value2 = "" Then

.Rows(i).Delete
End If
Next i
End Sub


Thanks for getting back to me, but I cant seem to get the code to work, attached is an example of the data that I am trying to sort, and remove the blank rows from.

Bob Phillips
11-27-2010, 09:58 AM
Sub DeleteBlankCells()
Dim Lastrow As Long
Dim i As Long

Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
With ActiveSheet

For i = Lastrow To 1 Step -1

If .Cells(i, "B").Value2 = "" Then

.Rows(i).Delete
End If
Next i
End With
End Sub


Put it in a standard code module, not a sheet module.

Panda
11-27-2010, 10:37 AM
ooops my bad, it works fine now although is there a way to define a range so that only the rows within that range get deleted. So in this example I would set the range for B4 to D13 and then only the empty rows within that range will be removed?

Thanks for your help btw =:)

Simon Lloyd
11-27-2010, 10:45 AM
If your data is truly as you have shown then this is faster:
Sub Del_Blank_Rows()
On Error Resume Next
ActiveSheet.UsedRange.EntireRow.SpecialCells(xlBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

EDIT: changed UsedRange for Range("B4: D13") to use a range. (space between : and D needs to be removed, it's only there because a smiley appears!

Bob Phillips
11-27-2010, 11:19 AM
EDIT: changed UsedRange for Range("B4: D13") to use a range. (space between : and D needs to be removed, it's only there because a smiley appears!Advanced dialog.

You can avoid that by disabling smilies in the AD

Simon Lloyd
11-28-2010, 12:50 PM
;), thanks Bob, i completely forgot about that, but when i have a little time (and i remember) i'll edit the smilie file on the server so that it's a different keystroke to bring that one up!