How to select columns in loop

[Guest]

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

 

[Bob Phillips]

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

 

[Tom Ogilvy]

When a row is deleted, it is deleted for all columns.

If you want anything other than that, you can do it with one command

ActiveSheet.Cells.SpecialCells(xlblanks).Delete

If you have more than 8192 discontiguous areas, then you might need a work
around.

 

[Guest]

Thanks you



JH

When a row is deleted, it is deleted for all columns.

If you want anything other than that, you can do it with one command

ActiveSheet.Cells.SpecialCells(xlblanks).Delete

If you have more than 8192 discontiguous areas, then you might need a work
around.