Selecting a cell then entire row (USMC)

[Hlatigo]

I have the following code and it looks like it should work but when I run it, it adds the rows at the very top of the selection and not inbetween yesterdays date and the forward date.

Brief explanation:
I am trying to add a row using column D as the determining factor. Column D has yesterdays date and all past dates and than it jumps to the next month and every forward month for two years or so.
11/7/2005
11/8/2005
11/9/2005
12/01/2005
01/01/2006
02/01/2006

So all i need to do is add a row in between 11/9/2005 and 12/01/2005

Any ideas? THANKS!! HAPPY MARINE CORPS BIRTHDAY!!! OOOOHRAAA!!

 

[Zack Barresse]

Oh, I'm sorry. I was testing using a With statement and forgot to take out the periods ..

    Dim lastRng as Range topRng as Range, i as Long
    Set lastRng = Cells(Rows.Count, "D").End(xlup)
    Set topRng = Range(lastRng, lastRng.End(xlup))
    For i = topRng.Cells.Count to 1 Step - 1
        If topRng(i).Value = DateAdd("d", -1, Format(Date, "mm-dd-yyyy")) Then
            topRng(1 + i).EntireRow.Insert '"1 + " inserts in row below
        End If
    Next i

Does that work for you?

 

[Hlatigo]

WOW! worked awesome!


The funny thing is, I looked at those periods and said to myself "i wonder if that had anything to do with it" And of course I didnt know, I was just guessing.

If in case you have time to respond again, do you know what could have been going wrong with the code i had originally? and why it would only add a row at the very top?

Thanks a million for your help!!

 

[Zack Barresse]

You're very welcome. Glad it works for you.

First of all, with your code, you are iterating through it going down. When you do a For Each cell In cellRange, Excel has a specific pattern it follows. In a multi-column/multi-row range, it will always start in the upper left and go to the right and go through the entire first row. Then it will drop to the left-most cell in the second row of the specified range and iterate to the right, etc, etc.

Now, with a single column linear array, it will iterate from the top down (only one cell per row). When you are inserting this is bad. You need to iterate from the bottom up. If you don't, as you iterate down, the range is being extended, but it's not defined as such (in lieu of you defining it before you start your iteration/loop) so you will miss some data, depending on how many rows you insert/delete/cut/whatever. If you start from the bottom, this combats such a byproduct as the default is to shift xlDown, and you're going up.

All of this and we still have to remember how Excel determines where to insert a row from the ActiveCell. If you have a range of data and click a cell manually, right click and select Insert Row (Shift:=xlDown), it will take everything from the selected cell and push it down, including the selected cell or specified range. This is important if we need to insert a row below the data/range specified.

HTH

 

[Hlatigo]

ray: very awesome and well understood. I appreciate all your help and time...many thanks!

latigo