Add/Delete rows based on count

[Mark K]

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

 

[crferguson]

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]

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]

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

 

[crferguson]

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]

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

 

[crferguson]

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]

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

Code
-------------------
'add rows
Do Until dRows = 23
ActiveCell.Offset(dRows + 1, 0).EntireRow.Inser
-------------------

The dRows +1 needed to be just dRows or it added the rows between th
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, al
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 row
then rows are added/deleted above the last row.

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