Delete empty rows from a sheet

[bm4466]

Hey could anybody help me with a script for deleting a row from a sheet if its second column is empty?

I would appreciate it, Ben

[pianoman]

rnum = 'insert start row number here

Range("A" & rnum).select
while activecell <>""
do
if activecell.offset(0,1) = "" then
entirerow.delete
else
rnum = rnum + 1
end if
Range("A" & rnum).select
loop until activecell = ""
Wend

Hey could anybody help me with a script for deleting a row from a sheet if its second column is empty?

I would appreciate it, Ben

[Ron de Bruin]

Hi bm4466

Try
http://www.rondebruin.nl/specialcells.htm

Sub DeleteBlankRows_2()
'This macro delete all rows with a blank cell in column B
'If there are no blanks or there are too many areas you see a MsgBox
Dim CCount As Long
On Error Resume Next

With Columns("B") ' You can also use a range like this Range("A1:A8000")

CCount = .SpecialCells(xlCellTypeBlanks).Areas(1).Cells.Count

If CCount = 0 Then
MsgBox "There are no blank cells"
ElseIf CCount = .Cells.Count Then
MsgBox "There are more then 8192 areas"
Else
.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End If

End With

On Error GoTo 0
End Sub


--
Regards Ron De Bruin
http://www.rondebruin.nl



"bm4466" <[email protected]> wrote in message
news:[email protected]...
>
> Hey could anybody help me with a script for deleting a row from a sheet
> if its second column is empty?
>
> I would appreciate it, Ben
>
>
> --
> bm4466
> ------------------------------------------------------------------------
> bm4466's Profile: http://www.excelforum.com/member.php...o&userid=33949
> View this thread: http://www.excelforum.com/showthread...hreadid=548594
>