Delete row?

  • Thread starter Thread starter ian123
  • Start date Start date
I

ian123

Hi guys, can anyone help with the following please?

Can i get a macro to search a selection of data and if it finds an
empty cell then the macro will delete the entire row in which that
empty cell is found?

ie, if i select column a, the macro searches until it finds an empty
cell in column a. Say an empty cell exists at a12, a23 and a45. In
turn the macro would delete rows 12, 23 and 45.

Thanks for your help
 
Dim c As Range
Dim x As Range
Set x = Selection
For Each c In x
If c.Value = "" Then
c.EntireRow.Delete
End If
Next c

- Pikus
 
Try this

This one can be a problem
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

Sub DeleteBlankRows()
On Error Resume Next 'In case there are no blank rows
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub


Or this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = ""
Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A, case sensitive. End If
Next
End With With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
 
Use this one without line wrap

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A, case sensitive.
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Ron de Bruin said:
Try this

This one can be a problem
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

Sub DeleteBlankRows()
On Error Resume Next 'In case there are no blank rows
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub


Or this one

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell ElseIf .Cells(Lrow, "A").Value = ""
Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A, case sensitive. End If
Next
End With With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Click to expand...
 
To be sure that you delete real empty row you can use Isempty

ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete
'This will delete each row if the cell is empty or have a formula that evaluates to ""

ElseIf IsEmpty(.Cells(Lrow, "A").Value) Then .Rows(Lrow).Delete
'This will delete the row if the cell is empty


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Ron de Bruin said:
Use this one without line wrap

Sub Example3()
Dim Lrow As Long
Dim CalcMode As Long
Dim StartRow As Long
Dim EndRow As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.DisplayPageBreaks = False
StartRow = 1
EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Lrow = EndRow To StartRow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in the cell
ElseIf .Cells(Lrow, "A").Value = "" Then .Rows(Lrow).Delete
'This will delete each row with the Value "ron" in Column A, case sensitive.
End If
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
Click to expand...
 
Pikus

I think this will fail when you have more than one blank together.

Try this as one alternative:

Dim cCell As Range
Dim xRangeSelected As Range
Dim yRangeToDelete As Range
Set xRangeSelected = Selection
For Each cCell In xRangeSelected
If cCell.Value = "" Then
If yRangeToDelete Is Nothing Then
Set yRangeToDelete = cCell
Else
Set yRangeToDelete = _
Union(yRangeToDelete, cCell)
End If
End If
Next cCell
If Not yRangeToDelete Is Nothing Then
yRangeToDelete.EntireRow.Delete
End If

Regards

Trevor
 
Cheers guys, so many options to choose from...!

I'll give each of them a go in turn and post back later to report on
which one works best in which circumstances should anyone want a
definitive answer.

Once again, many thanks.
 
Back
Top