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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.