Automation for inserting blank rows

**phong919** · 07-09-2008, 03:35 PM

Hello all,

I'm trying to insert a blank row at the end of the last finish date.

If the last row is 6/1/08, then i would like to insert a blank row that would say July 08

I've attached a sample spreadsheet. any assistance would be greatly appreciated.

**Richard Buttrey** · 07-09-2008, 05:38 PM

Hi,

It's not clear (at least to me) what are your exact requirements.

The sample data has the dates in chronological order, hence the last date is the last row used - in this case row 117.
What do you mean by insert a row? Above row 117 or do you want to use the blank row 118?

Do I interpret 6/1/08 to mean June 1st 2008, or to those of us on the Eastern bank of the Atlantic, 6th January 2008?

If you do mean 6th January, (which seems consistent with the other dates in column F), how do you get from that January date to July 08?

When you say the blank row should say July 08, have you a particular cell in mind - presumably you mean the Finish Date column F, which implies F118, and do you want the text 'July 08' or an excel date number?

Regards

**Rick_Stanich** · 07-09-2008, 06:01 PM

The dates appear to be mm\dd\yy format.
Does this do what you want? It will place a value in row 118 column "A" of your worksheet as "July-09", the last date in the row is 07/15/09.

Option Explicit

Sub aTest()
Dim ShLastRow As Variant
Dim ShRange As Range
Dim ShCell As Range
Dim vCount As Variant
Dim vAddress As Variant

    vCount = "1" 'reset counter to 1
    With Sheets(1) 'count rows
        ShLastRow = .Cells(Rows.Count, "F").End(xlUp).Row
        Set ShRange = .Range("F2:F" & ShLastRow)
    End With

    For Each ShCell In ShRange    'get data from sheet(1)
        If IsDate(ShCell.Value) = True Then
            vCount = vCount + 1
            vAddress = ShCell.Address
        End If
    Next ShCell
Range(vAddress).Offset(1, -5).Value = Range(vAddress).Value
Range(vAddress).Offset(1, -5).NumberFormat = "[$-409]mmmm-yy;@"
End Sub

**phong919** · 07-10-2008, 01:04 PM

Hello,

Thank you for your assistance...

Let me try to clarified more.....

For example....in line 87 the end date is 11/30/2008 and then line 88 is 12/1/2008. I wanted to insert in between those 2 lines a blank row displaying December 2008 in bold and the row highlighted in yellow.

I want to do this starting from the top and go through all the end dates and insert the appropriate values.

hope that helps. thank you again.

Automation for inserting blank rows

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Automation for inserting blank rows

Automation for inserting blank rows

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions