Delete variable # of rows depending on conditions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pman - 26 Sep 2007 21:25 GMT

Hi,

I have a data sheet in which I have written a macro to add up columns upto
row #503. However every month I do not need 503 rows, sometimes its as small
as 20, or sometimes I need all the 503 rows. I was wondering if anyone could
help me figure out as to how to delete rows if more than three consecutive
rows are blank (this is because there is a blank row in between every unique
entity in a cell).
For eg: column "B" has data as follows:
1
1
1

2
2

3

4
4
4

TOTAL

If I use a macro to delete rows, it deletes the rows between the unique
values as well....hence I thought if a macro can be written that can delete
the blank rows till my total.

Thanks in Advance.....and Thanks to everyone who has helped me in the past :)

P

Reply to this Message

Charles Chickering - 27 Sep 2007 00:23 GMT

Pman, if you're trying to leave only one blank row in at a time try something
like this:
Dim cnt as Long
Dim MyCell as Range
For cnt = 503 to 2 Step -1
Set MyCell = Range("B" & cnt)
If MyCell = "" and MyCell.Offset(-1) = "" Then MyCell.EntireRow.Delete
Next

Signature

Charles Chickering

"A good example is twice the value of good advice."

> Hi,
>
[quoted text clipped - 27 lines]
>
> P

Reply to this Message

Joel - 27 Sep 2007 03:22 GMT

Your problem is finding the last row of data. Use this statement

Lastrow = cells(rows.count,"A").end(xlup).Row

row.count is an excel constant which is 65536.
end(xlup) move from 65536 to the first non empty cell

You can then modify your loop

for i = 2 to LastRow

next i

> Pman, if you're trying to leave only one blank row in at a time try something
> like this:
[quoted text clipped - 36 lines]
> >
> > P

Reply to this Message

Charles Chickering - 27 Sep 2007 04:11 GMT

I'm well aware of the .End(xlUp) function. The OP stated that he had 503 rows
to deal with, therefore no need for the extra processing time of finding the
last used row. Furthermore, .End(xlUp) only works if all rows are visible.
Taking this one step further, we're deleting rows so if we work from row 2 to
the end we will end up skipping rows causing the OP further problems. When
deleting rows you should always work from the bottom up.

Signature

Charles Chickering

"A good example is twice the value of good advice."

> Your problem is finding the last row of data. Use this statement
>
[quoted text clipped - 49 lines]
> > >
> > > P

Reply to this Message

Joel - 27 Sep 2007 04:23 GMT

You are fixing the wrong problem. Look a again at the data. He is putting
the row with TOTAL at row 504 even if there is on 20 rows of data. He simply
wants to put the row containing TOTAL at the end of the list. The blank rows
he is trying to eliminate is between the end of the data and the row with
TOTAL.

> I'm well aware of the .End(xlUp) function. The OP stated that he had 503 rows
> to deal with, therefore no need for the extra processing time of finding the
[quoted text clipped - 56 lines]
> > > >
> > > > P

Reply to this Message

Pman - 27 Sep 2007 15:27 GMT

Thank you Charles :)
It worked for me :)

> Pman, if you're trying to leave only one blank row in at a time try something
> like this:
[quoted text clipped - 36 lines]
> >
> > P

Reply to this Message