Add/Delete rows based on count

Hi all.

I have blocks of data where column A is arranged as follows (note the 'name' will all be different, The text 'Starts:' is constant):

Name1
Starts:
rows of data (number of rows varies from 0 to 100 or more)
Name2
Starts:
rows of data
Name3
Starts:
rows of data
Name4
Starts:
rows of data
Name5
Starts:
rows of data

and so on, for up to 20-30 name blocks.

What I'm trying to do is to get the number of rows between a cell containg 'Starts:' and the next name in column A to = 22, including 2 empty rows at the bottom.

So if the number of 'data' rows is less than 20 (some blocks will have 0 rows of data), I need to pad it out with empty rows, but if it's more than 20 I need to delete the excess rows (starting at the bottom of the block). I then need to add an additional 2 empty rows so I end up with the following:

A1: Name1
A2: Starts:
A3 to A22: data/empty rows
A23: empty row
A24: empty row
A25: Name2
A26: Starts:
A27 to A46: data/empty rows
A47: empty row
A48: empty row
A49: Name3
A50: Starts:
A51 to A70: data/empty rows
A71: empty row
A72: empty row

All the way down the sheet.

I have got as far as figuring out how to add the 2 empty rows I need but have no idea where to head on the rest. Note that while I have only used column A in the example, entire rows need to be considered as other columns also contain data.

Yous say that "entire rows need to be considered as other columns also
contain data." Does this mean you want to delete the entire row (if it
needs to be deleted)? Or are you saying to be careful not to delete
the other columns because they might have data in them?



Mark K wrote:
> Hi all.
>
> I have blocks of data where column A is arranged as follows (note the
> 'name' will all be different, The text 'Starts:' is constant):
>
> Name1
> Starts:
> rows of data (number of rows varies from 0 to 100 or more)
> Name2
> Starts:
> rows of data
> Name3
> Starts:
> rows of data
> Name4
> Starts:
> rows of data
> Name5
> Starts:
> rows of data
>
> and so on, for up to 20-30 name blocks.
>
> What I'm trying to do is to get the number of rows between a cell
> containg 'Starts:' and the next name in column A to = 22, including 2
> empty rows at the bottom.
>
> So if the number of 'data' rows is less than 20 (some blocks will have
> 0 rows of data), I need to pad it out with empty rows, but if it's more
> than 20 I need to delete the excess rows (starting at the bottom of the
> block). I then need to add an additional 2 empty rows so I end up with
> the following:
>
> A1: Name1
> A2: Starts:
> A3 to A22: data/empty rows
> A23: empty row
> A24: empty row
> A25: Name2
> A26: Starts:
> A27 to A46: data/empty rows
> A47: empty row
> A48: empty row
> A49: Name3
> A50: Starts:
> A51 to A70: data/empty rows
> A71: empty row
> A72: empty row
>
> All the way down the sheet.
>
> I have got as far as figuring out how to add the 2 empty rows I need
> but have no idea where to head on the rest. Note that while I have only
> used column A in the example, entire rows need to be considered as other
> columns also contain data.
>
>
> --
> Mark K
> ------------------------------------------------------------------------
> Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117
> View this thread: http://www.excelforum.com/showthread...hreadid=573244

Originally Posted by crferguson@gmail.com

Yous say that "entire rows need to be considered as other columns also
contain data." Does this mean you want to delete the entire row (if it
needs to be deleted)? Or are you saying to be careful not to delete
the other columns because they might have data in them?

Sorry if that was confusing, but yes, entire row to be deleted if it needs to be. Meant to point out that the row needs to be deleted, not just the cell. Teach me to be posting at 6am after an all-nighter trying to figure this out.

OK, it appears dealing with this one all at once may be too much, so I'll start working on one thing at a time, coming back when I can't figure out how to do something.

First off, how do I locate the first occurance of a cell in column "A" containing the text "Starts:" ? Once that's done I will be able to use that cell as a start point for other actions and, hopefully, be able to do a lot on my own.

Sorry I've been absent from the Internet for a couple of days...

To answer your question, I know there may be a better way of doing
this, but try somethig simple like:

Range("A1").Select

Do Until Activecell.Value = "Start"
Activecell.Offset(1,0).Select
Loop

Mark K wrote:
> OK, it appears dealing with this one all at once may be too much, so
> I'll start working on one thing at a time, coming back when I can't
> figure out how to do something.
>
> First off, how do I locate the first occurance of a cell in column "A"
> containing the text "Starts:" ? Once that's done I will be able to use
> that cell as a start point for other actions and, hopefully, be able to
> do a lot on my own.
>
>
> --
> Mark K
> ------------------------------------------------------------------------
> Mark K's Profile: http://www.excelforum.com/member.php...o&userid=14117
> View this thread: http://www.excelforum.com/showthread...hreadid=573244

Thanks for that - quick and simple.

So from there I could set it and do something like:

Set sts = ActiveCell
sts.Offset(5, 3).Select

to select a cell 5 rows down, 3 columns over as the active cell. But sts would still equal the original cell correct? Sorry if this seems a little simple, but I'm still only just learning.