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

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

--
Charles Chickering

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


"Pman" wrote:

> 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

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

"Charles Chickering" wrote:

> 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
>
> --
> Charles Chickering
>
> "A good example is twice the value of good advice."
>
>
> "Pman" wrote:
>
> > 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

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.
--
Charles Chickering

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


"Joel" wrote:

> 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
>
> "Charles Chickering" wrote:
>
> > 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
> >
> > --
> > Charles Chickering
> >
> > "A good example is twice the value of good advice."
> >
> >
> > "Pman" wrote:
> >
> > > 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

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.

"Charles Chickering" wrote:

> 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.
> --
> Charles Chickering
>
> "A good example is twice the value of good advice."
>
>
> "Joel" wrote:
>
> > 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
> >
> > "Charles Chickering" wrote:
> >
> > > 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
> > >
> > > --
> > > Charles Chickering
> > >
> > > "A good example is twice the value of good advice."
> > >
> > >
> > > "Pman" wrote:
> > >
> > > > 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

Thank you Charles
It worked for me

"Charles Chickering" wrote:

> 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
>
> --
> Charles Chickering
>
> "A good example is twice the value of good advice."
>
>
> "Pman" wrote:
>
> > 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