[Ayo]

I have this code that I am running and there are a few issues that I can't
figure out, maybe someone out there can help. The code below is asking to
delete rows where the selected cell or the one next to it are blanks. My
problem is that when I run it, only 2 rows are delete even though there are 4
consecutive rows having the criteria of the "IF" condition met. The real fact
is that the code only delete every other row. Say for eaxmple I have this
four rows with in which the condition is true, only rows containing 2YK3342
and 2DA3328 are deleted. Leaving the rows with 2CN3434 and CT11058.
2YK3342
2CN3434
2DA3328
CT11058
For Each c In Range("C2:C" & dbelastRow).Cells
c.Select
If c.Value = "" Or c.Offset(0, 1).Value = "" Then
ActiveCell.EntireRow.Delete
End If
Next c

The other issue that I am having is the fact that in the following code
segment, the line: ActiveCell.EntireRow.Delete don't do anything even when
the if condition is true. It works fine in the code segment above, except it
skip every other row but it don't work in the code below at all.
Any ideas?
Thanks

For Each c In Range("A2:A" & dbelastRow).Cells
c.Select
If c.Value = marketName Then
strRow = Mid(c.Address(RowAbsolute:=False), 3)
For Each c1 In Range("A" & strRow & ":A" & dbelastRow).Cells
If c1.Value < marketName Then
endRow = Mid(c1.Address(RowAbsolute:=False), 3) - 1
Exit For
End If
Next c1
ElseIf c.Value < marketName Then
ActiveCell.EntireRow.Delete
'ActiveCell.Rows.Select
'Selection.Delete Shift:=xlUp
End If
Next c

[Mike H]

Hi,

First part. If your deleting rows 'on the fly' then you have to work
backwards through the range. To see why select A1 on a sheet and delete row 1
and you will note the selected cell is still in row one (which was row 2) so
it will be missed in a forward running look. Try this

For x = dbelastRow To 2 Step -1
If Cells(x, 3).Value = "" Or Cells(x, 3).Offset(0, 1).Value = "" Then
Rows(x).EntireRow.Delete
End If
Next x

You second loop works for me with the same caveat as above you will have to
modify it to run backwards.

Mike

"Ayo" wrote:

I have this code that I am running and there are a few issues that I can't
figure out, maybe someone out there can help. The code below is asking to
delete rows where the selected cell or the one next to it are blanks. My
problem is that when I run it, only 2 rows are delete even though there are 4
consecutive rows having the criteria of the "IF" condition met. The real fact
is that the code only delete every other row. Say for eaxmple I have this
four rows with in which the condition is true, only rows containing 2YK3342
and 2DA3328 are deleted. Leaving the rows with 2CN3434 and CT11058.
2YK3342
2CN3434
2DA3328
CT11058
For Each c In Range("C2:C" & dbelastRow).Cells
c.Select
If c.Value = "" Or c.Offset(0, 1).Value = "" Then
ActiveCell.EntireRow.Delete
End If
Next c

The other issue that I am having is the fact that in the following code
segment, the line: ActiveCell.EntireRow.Delete don't do anything even when
the if condition is true. It works fine in the code segment above, except it
skip every other row but it don't work in the code below at all.
Any ideas?
Thanks

For Each c In Range("A2:A" & dbelastRow).Cells
c.Select
If c.Value = marketName Then
strRow = Mid(c.Address(RowAbsolute:=False), 3)
For Each c1 In Range("A" & strRow & ":A" & dbelastRow).Cells
If c1.Value < marketName Then
endRow = Mid(c1.Address(RowAbsolute:=False), 3) - 1
Exit For
End If
Next c1
ElseIf c.Value < marketName Then
ActiveCell.EntireRow.Delete
'ActiveCell.Rows.Select
'Selection.Delete Shift:=xlUp
End If
Next c

[Ayo]

Thanks Mike. It worked.

"Mike H" wrote:

Hi,

First part. If your deleting rows 'on the fly' then you have to work
backwards through the range. To see why select A1 on a sheet and delete row 1
and you will note the selected cell is still in row one (which was row 2) so
it will be missed in a forward running look. Try this

For x = dbelastRow To 2 Step -1
If Cells(x, 3).Value = "" Or Cells(x, 3).Offset(0, 1).Value = "" Then
Rows(x).EntireRow.Delete
End If
Next x

You second loop works for me with the same caveat as above you will have to
modify it to run backwards.

Mike

"Ayo" wrote:

I have this code that I am running and there are a few issues that I can't
figure out, maybe someone out there can help. The code below is asking to
delete rows where the selected cell or the one next to it are blanks. My
problem is that when I run it, only 2 rows are delete even though there are 4
consecutive rows having the criteria of the "IF" condition met. The real fact
is that the code only delete every other row. Say for eaxmple I have this
four rows with in which the condition is true, only rows containing 2YK3342
and 2DA3328 are deleted. Leaving the rows with 2CN3434 and CT11058.
2YK3342
2CN3434
2DA3328
CT11058
For Each c In Range("C2:C" & dbelastRow).Cells
c.Select
If c.Value = "" Or c.Offset(0, 1).Value = "" Then
ActiveCell.EntireRow.Delete
End If
Next c

The other issue that I am having is the fact that in the following code
segment, the line: ActiveCell.EntireRow.Delete don't do anything even when
the if condition is true. It works fine in the code segment above, except it
skip every other row but it don't work in the code below at all.
Any ideas?
Thanks

For Each c In Range("A2:A" & dbelastRow).Cells
c.Select
If c.Value = marketName Then
strRow = Mid(c.Address(RowAbsolute:=False), 3)
For Each c1 In Range("A" & strRow & ":A" & dbelastRow).Cells
If c1.Value < marketName Then
endRow = Mid(c1.Address(RowAbsolute:=False), 3) - 1
Exit For
End If
Next c1
ElseIf c.Value < marketName Then
ActiveCell.EntireRow.Delete
'ActiveCell.Rows.Select
'Selection.Delete Shift:=xlUp
End If
Next c