Hi all.

I have blocks of data where column A is arranged as follows (note th
'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 cel
containg 'Starts:' and the next name in column A to = 22, including
empty rows at the bottom.

So if the number of 'data' rows is less than 20 (some blocks will hav
0 rows of data), I need to pad it out with empty rows, but if it's mor
than 20 I need to delete the excess rows (starting at the bottom of th
block). I then need to add an additional 2 empty rows so I end up wit
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 nee
but have no idea where to head on the rest. Note that while I have onl
used column A in the example, entire rows need to be considered as othe
columns also contain data

--
Mark
-----------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411
View this thread: http://www.excelforum.com/showthread.php?threadid=57324

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

Wrote:
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. :)


--
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

OK, it appears dealing with this one all at once may be too much, s
I'll start working on one thing at a time, coming back when I can'
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 us
that cell as a start point for other actions and, hopefully, be able t
do a lot on my own

--
Mark
-----------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411
View this thread: http://www.excelforum.com/showthread.php?threadid=57324

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. Bu
sts would still equal the original cell correct? Sorry if this seems
little simple, but I'm still only just learning

--
Mark
-----------------------------------------------------------------------
Mark K's Profile: http://www.excelforum.com/member.php...fo&userid=1411
View this thread: http://www.excelforum.com/showthread.php?threadid=57324