Code Tweak

Columns("A").SpecialCells(xlBlanks).Offset(-1).Select

This code does what I want it to, It will select all blank spaces, then
select one cell above it. Now, i want to take it a step further and have it
select every row from that one up to the next blank above it. And if the
selection does not equal 30 spaces, I want it to add blank rows to it to
become 30 rows. SO for example

The selection is 14 rows, I want it to add 16 empty rows. Make sense???

Range(ActiveCell, ActiveCell(30 - _ActiveCell.Value, 1)).EntireRow.Insert

Wil this work for part of it?

So what will the complete code look like? Thanks!!!!!!

This seems like a pretty weird question. I'm gonna guess that you want to make
sure that there's 30 empty rows between each group--and your question was just a
simplified version of that (and that you'd modify the code to do the looping).

If I'm right, keep reading.

I'm gonna assume that all my cells in column A are constants or empty--no
formulas!

If that's the case, this worked ok for me:

Option Explicit
Sub testme()
Dim myAreas As Range
Dim aCtr As Long
Dim myRng As Range
Dim myConstRng As Range
Dim TopRowOfBottomArea As Long
Dim BotRowOfTopArea As Long
Dim HowManyRowsBetween As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set myRng = .Range("a:a")
Set myConstRng = Nothing
On Error Resume Next
Set myConstRng = myRng.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myConstRng Is Nothing Then
MsgBox "no constants!"
Exit Sub
End If

For aCtr = myConstRng.Areas.Count To 2 Step -1
TopRowOfBottomArea = myConstRng.Areas(aCtr).Row
With myConstRng.Areas(aCtr - 1) 'area just on top
BotRowOfTopArea = .Cells(.Cells.Count).Row
End With

HowManyRowsBetween = TopRowOfBottomArea - BotRowOfTopArea - 1

If HowManyRowsBetween >= 30 Then
'do nothing--not even delete extra rows???
Else
.Rows(TopRowOfBottomArea - 1) _
.Resize(30 - HowManyRowsBetween).Insert
End If
Next aCtr
End With

End Sub

This essentially selects column A and does
edit|goto|special cells|constants

Then it starts at the bottom and looks at each of the little areas within column
A.

It looks at the top row of the bottom area and the bottom row of the area just
on top of it. And cycles from the bottom up.



bodhisatvaofboogie wrote:
>
> Columns("A").SpecialCells(xlBlanks).Offset(-1).Select
>
> This code does what I want it to, It will select all blank spaces, then
> select one cell above it. Now, i want to take it a step further and have it
> select every row from that one up to the next blank above it. And if the
> selection does not equal 30 spaces, I want it to add blank rows to it to
> become 30 rows. SO for example
>
> The selection is 14 rows, I want it to add 16 empty rows. Make sense???
>
> Range(ActiveCell, ActiveCell(30 - _ActiveCell.Value, 1)).EntireRow.Insert
>
> Wil this work for part of it?
>
> So what will the complete code look like? Thanks!!!!!!

--

Dave Peterson