Delete Consecutive Rows if counta(row #) is =
 

Hi,

I want to remove all the rows in used cells, when there are only 2
filled cells in any row. I am using following code

ActiveSheet.UsedRange.Select
For Each rw In Selection.Rows
If WorksheetFunction.CountA(rw.EntireRow) = 2 Then
rw.EntireRow.Delete
End If
Next rw

My Problem is - If there there are two consecutive rows satisfing this
criterion, only one gets deleted. Can someone please suggest me an
efficient approach to do that. I want to avoid counters in loop to
make it good in performance. So if there is any row based or array
based logic, that can help me solve this, please share with me..


Thanks!

Delete Consecutive Rows if counta(row #) is =
 

Hi Avi

When you delete rows always start at the botom an go up

See
http://www.rondebruin.nl/delete.htm



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Avi" wrote in message ...
Hi,

I want to remove all the rows in used cells, when there are only 2
filled cells in any row. I am using following code

ActiveSheet.UsedRange.Select
For Each rw In Selection.Rows
If WorksheetFunction.CountA(rw.EntireRow) = 2 Then
rw.EntireRow.Delete
End If
Next rw

My Problem is - If there there are two consecutive rows satisfing this
criterion, only one gets deleted. Can someone please suggest me an
efficient approach to do that. I want to avoid counters in loop to
make it good in performance. So if there is any row based or array
based logic, that can help me solve this, please share with me..


Thanks!