can't find error in macro logic...

[tehwa]

I have a piece of mcro code that is giving me grief, but I cannot fin
the error in the logic:

The purpose is to loop through the second column until there is no mor
data in the column, searching for nulls. If there is a null in a cel
from that column, the row needs to be deleted. But it will not detec
consecutive null cells, so it is only deleting every second row that
want it to.

'Find the end of the range, to set the bounds of th
function:

Dim areaCount As Integer

Columns(1).Select
ActiveCell.CurrentRegion.Select
areaCount = Selection.Rows.Count
Dim counter As Integer

'Loop through from (1, 2) to (end, 2) deleting rows with null in th
cells.

For counter = 1 To areaCount
If Cells(counter, 2).Value = 0 Then
Rows(counter).Delete
End If
Next counter

So it should be going:

Is (1, 2) null? Nope. Next cell.
Is (2,2) null? Nope, Next cell
Is (3, 2) null? Yep, Delete the row. Next Cell...

If anyone can tell me where I am going wrong here, or just paste
working algorithm I would be very grateful.

.tehw

 

[mudraker]

you need to step backwards from the last row to the first


Dim areaCount As Integer

Columns(1).Select
ActiveCell.CurrentRegion.Select
areaCount = Selection.Rows.Count
Dim counter As Integer

'Loop through from (end, 2) to (1, 2) deleting rows with null in th
cells.

For counter = areaCount To 1 step -1
If Cells(counter, 2).Value = 0 Then
Rows(counter).Delete
End If
Next counte

 

[tehwa]

ah, thanks!

That's interesting. Why won't it work the other way 'round

 

[Ken Wright]

The reason it bombs is because you are working downwards. Suppose you have 6
rows as follows:-

1 Data
2 Data
3 Blank
4 Blank
5 Blank
6 Data

Your code runs and starts at row 1 say, it has data so it moves on to row 2. It
has data so it moves on to row 3 - It is blank so it deletes it and then moves
on to row 4 BUT, what WAS row 4 is now row 3 because you just deleted a row, and
so row 3 is now blank but doesn't get deleted because your routine has passed
it, and so on.

Determine the last cell and then work backwards which will not be affected by
this problem, eg:-

Sub DelRows()

Dim r As Long
Dim lrow As Long

lrow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

For r = lrow To 1 Step -1
With Cells(r, 1)
If .Value = "" Then
.EntireRow.Delete
End If
End With
Next r
End Sub

You can also use the following to get the last row in a specific column:-

lrow = Cells(Rows.Count, "H").End(xlUp).Row

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

I have a piece of mcro code that is giving me grief, but I cannot find
the error in the logic:

The purpose is to loop through the second column until there is no more
data in the column, searching for nulls. If there is a null in a cell
from that column, the row needs to be deleted. But it will not detect
consecutive null cells, so it is only deleting every second row that I
want it to.

'Find the end of the range, to set the bounds of the
function:

Dim areaCount As Integer

Columns(1).Select
ActiveCell.CurrentRegion.Select
areaCount = Selection.Rows.Count
Dim counter As Integer

'Loop through from (1, 2) to (end, 2) deleting rows with null in the
cells.

For counter = 1 To areaCount
If Cells(counter, 2).Value = 0 Then
Rows(counter).Delete
End If
Next counter

So it should be going:

Is (1, 2) null? Nope. Next cell.
Is (2,2) null? Nope, Next cell
Is (3, 2) null? Yep, Delete the row. Next Cell...

If anyone can tell me where I am going wrong here, or just paste a
working algorithm I would be very grateful.

tehwa

 

[Ken Wright]

See the example in my reply for the reason

 

[mudraker]

when you delete row 5 row 6 becomes the new row 5.
your macro has already checked row 5 so does not look at row 5 again
and moves onto row 6

when you go backwards when you delete row 5 and row 6 becomes the new
row 5 - you would have already checked the new row 5 when it was still
row 6, your macro then moves down to row 4

 

[tehwa]

Excellent, thanks.

I think I need to go back to studying how the program actually works...

 

[Ken Wright]

Did you mean to post that in reply to tehwa's note?