Sub delEmptyrow()
Dim cLastRow As Long
Dim iRow As Long, iCol As Long

cLastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For iCol = 1 To 10
For iRow = cLastRow To 1 Step -1
If Cells(iRow, iCol).Value = "" Then
Cells(iRow, iCol).Delete Shift:=xlUp
End If
Next iRow
Next iCol

End Sub


--
HTH

Bob Phillips

"JH" wrote in message
...
Dear experts,

please I need to help with following problem:

I 've got in a worksheet 10 columns. Start A1.
I'd like to remove empty cells in each column.
to remove empty cells I use following:

Sub delEmptyrow()
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r

End Sub

this work fine but just for one column
I don't know how to select each column separately in loop in order to use
"my Sub"

actually, how to replace this notation range("A:A")

thanks

JH