Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete variable # of rows depending on conditions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete variable # of rows depending on conditions
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete variable # of rows depending on conditions
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete variable # of rows depending on conditions
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete variable # of rows depending on conditions
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete variable # of rows depending on conditions
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to write to macro to only delete rows under certain conditions | Excel Programming | |||
Delete rows depending on cell Value | Excel Programming | |||
How to delete a set of rows depending on Value | Excel Discussion (Misc queries) | |||
Insert rows in depending on criteria in variable columns | Excel Programming | |||
Delete all Rows in a Variable Range | Excel Programming |