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.

Yes, what you said works in limited situations. I'd try setting sts as
a string and using the activecell.address instead. Errors can arise if
you're not specific about what you're working with and even using the
Object or Variant type, things can get funky with what you suggest.
But, for your specific example it works.

I've been playing with a little code going back to your original
question. This should do what you're describing, though it's a little
messy:

Public Sub AddRemoveRows()
Dim dRows As Double, dLRow As Double, dSRow As Double
Dim sCell As String

Range("A1").Select

Do Until Trim(LCase(ActiveCell.Value)) = LCase("starts:")
ActiveCell.Offset(1, 0).Select
Loop

sCell = ActiveCell.Address
dSRow = ActiveCell.Row - 2

Do Until ActiveCell.Value = Empty
dRows = 0
dLRow = ActiveSheet.UsedRange.Rows.Count + dSRow
'go to next "Start: " in prep of editing rows
'or exit if last row is reached
ActiveCell.Offset(1, 0).Select
Do Until Trim(LCase(ActiveCell.Value)) = LCase("starts:") Or _
ActiveCell.Row > dLRow
ActiveCell.Offset(1, 0).Select
dRows = dRows + 1
Loop

'add / delete rows depending on dRows
Range(sCell).Select
If dRows = 1 Then
dRows = 0
Do Until dRows = 22
ActiveCell.Offset(dRows + 1, 0).EntireRow.Insert
shift:=xlDown
dRows = dRows + 1
Loop
ElseIf dRows < 23 Then
'add rows
Do Until dRows = 23
ActiveCell.Offset(dRows + 1, 0).EntireRow.Insert
shift:=xlDown
dRows = dRows + 1
Loop
ElseIf dRows > 23 Then
'delete rows
Dim s As String
Do Until dRows = 23
ActiveCell.Offset(dRows - 3, 0).EntireRow.Delete
shift:=xlUp
dRows = dRows - 1
Loop
End If
'clear the last two of the 22 rows
Range(ActiveCell.Offset(dRows - 2, 0).Address,
ActiveCell.Offset(dRows - 1, 0).Address).EntireRow.ClearContents
'set the starting cell address to the next "Start: "
sCell = ActiveCell.Offset(24, 0).Address
Range(sCell).Select
Loop
Range("A1").Select
End Sub



Mark K wrote:
> 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.
>
>
> --
> 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

Thank you - almost perfect. Have come across 2 minor glitches. First one I fixed myself:

Please Login or Register  to view this content.

The dRows +1 needed to be just dRows or it added the rows between the name and starts.

The second glitch has me beat. It only affects the last block of data. No matter how many rows of data are in the last set of records, all actions are performed above the last row. So if there are no data rows, rows are added between the name and starts row. If there are data rows then rows are added/deleted above the last row.

Not a real problem if there's more than 20 rows to work with, but not good with less than 20 rows. I've tried to figure it out myself but just don't have the knowledge. Any fix would be great.