how to delete empty row in vba
 

I have one excel spreed sheet, I need to delete all the
empty row, also some of cell in columnF if is the space,
then this record need to delete as well.
Anyone know how to write the vba macro?

thanks.

Lillian

how to delete empty row in vba
 

Hi

Please stay in your other thread

Run the TRIMALL macro I posted in that thread
And run this macro if you want to delete each row if the whole row is empty.


Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells(xlCellTypeBlanks).EntireRow

For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Lillian" wrote in message ...
I have one excel spreed sheet, I need to delete all the
empty row, also some of cell in columnF if is the space,
then this record need to delete as well.
Anyone know how to write the vba macro?

thanks.

Lillian

how to delete empty row in vba
 

Hi,

I did not konw how to run that TRIMALL macro, as I said
in the previous mail, I need to you told me too, still
not working, but I use the below script, all the empty
row is gone, that what I like it, thanks.

Now I need to delete the record when each rows of
columnF is space (" "), how to do that?


-----Original Message-----
Hi

Please stay in your other thread

Run the TRIMALL macro I posted in that thread
And run this macro if you want to delete each row if the
whole row is empty.


Sub DeleteEmptyRows()
'Dave Braden
Dim l As Long, rng As Range, rngDel As Range

On Error Resume Next
Set rng = ActiveSheet.UsedRange
With rng.Columns
Set rngDel = .Item(1).SpecialCells
(xlCellTypeBlanks).EntireRow

For l = 2 To .Count
Set rngDel = Intersect(rngDel, _
.Item(l).SpecialCells
(xlCellTypeBlanks).EntireRow)
If rngDel Is Nothing Then Exit Sub
Next
End With
Application.ScreenUpdating = False
rngDel.Delete
End Sub




--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Lillian" wrote in message
...
I have one excel spreed sheet, I need to delete all the
empty row, also some of cell in columnF if is the
space,
then this record need to delete as well.
Anyone know how to write the vba macro?

thanks.

Lillian


.