Excel 2010 vba issues with code from 2003-2007

[JR869]

I searched and came up with practically nothing.

I have a handle on most of what needs modified in the code Stanley helped me with here http://www.ozgrid.com/forum/showthre...960#post515960

However, if there is some advice on what else needs modified to work with XL2010 regarding the code below, I'd appreciate it; it would go far to nudging me in the right direction for modifying the rest of my code for 2 different projects I 'thought' I was close to completing. I would appreciate any advice or commentary on backwards-compatibility as well; if there are issues, I'm just going to make it for XL2010 and get the users upgraded. If there is a reference to 2010 'issues' I could get pointed to, that would be appreciated as well, as I'm not finding much that's very helpful. This is a HUGE step backward for me, given that we have limited XL2007 licenses and loaded up with XL2010 licenses.

Thanks in advance!

JR

Original code submitted by Stanley

Sub Insert_Blank_RowsV2() 
     ' Stanley D. Grom, 07/29/2010, [URL]http://www.ozgrid.com/forum/showthread.php?t=144807[/URL]
    Dim rng As Range, cell As Range, LR As Long 
    Application.ScreenUpdating = False 
    LR = Cells(Rows.Count, "A").End(xlUp).Row 
    Range("A2:N" & LR).Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 
    Columns("A:B").Insert 
    [A2] = 1 
    Columns(12).Copy Columns(2) 
    Set rng = Range([A3], [B65536].End(xlUp)(1, 0)) 
    With rng 
        .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C,R[-1]C+1)" 
        .Value = .Value 
    End With 
    Set cell = [A65536].End(xlUp) 
    cell(2) = 1 
    cell(2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cell - 1 
    Range([A2], [A65536].End(xlUp)).EntireRow.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo 
    Columns("A:B").Delete 
    Application.ScreenUpdating = True 
End Sub 

I got as far as correcting the syntax for XL2010 with this

Sub Insert_Blank_RowsV2() 
     ' Stanley D. Grom, 07/29/2010, [URL]http://www.ozgrid.com/forum/showthread.php?t=144807[/URL]
    Dim rng As Range, cell As Range, LR As Long 
    Application.ScreenUpdating = False 
    LR = Cells(Rows.Count, "A").End(xlUp).Row 
    Range("A2:N" & LR).Sort Key1:=Range("J2"), Order1:=xlAscending, Header:=xlNo, _ 
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal 
    Columns("A:B").Insert 
    Range("A2") = 1 
    Columns(12).Copy Columns(2) 
    Set rng = Range("A3", "B65536").End(xlUp)(1, 0) 
    With rng 
        .FormulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C,R[-1]C+1)" 
        .Value = .Value 
    End With 
    Set cell = Range("A65536").End(xlUp) 
    cell(2) = 1 
    cell(2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cell - 1 
    Range("A2", "A65536").End(xlUp).EntireRow.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlNo 
    Columns("A:B").Delete 
    Application.ScreenUpdating = True 
End Sub 

(Note: I figured out the brackets are 'out' for 2010 and the basic range formatting that 2010 is anal about, but I'm getting errors [Application-defined or Object-defined 1004 error] here

Set rng = Range("A3", "B65536").End(xlUp)(1, 0) 

I have a feeling it has to do something with this http://support.microsoft.com/kb/244435 . I tried searching this site for references I've read about making code compatible with future releases but could not find anything in a half-dozen passes)

[JR869]

Ok. I figured out the syntax wanted '.Offset' (I think...it fixed the error). But this brings me back to the original problem I had prior: It 'sorts' but no longer inserts the blank rows.

I think it's related to the R1C1 formula, as I don't see the code continuing down farther than row 3 in break mode while the column is inserted.

Any thoughts?

[S M C]

Set rng = Range(Range("A3"), Range("B" & Rows.Count).End(xlUp).Offset(1, 0)) 

[JR869]

I attached a workbook with sample data and the formulae from Stanley in Module 1. I 'may' have misread his reply in that he intended that the 'Sub Z_Insert_Blank_Rows_DoesWork_000()' code was the working code as opposed to the 'Sub Insert_Blank_RowsV2()', but his code (V2) DID do the trick (at least in XL2007 on my system at work).

I never did get the prior code (Sub_Z) working at all.

JR

Note: By default, the data is sorted descending in Column A (Aged Days), as that is the format exported out of our database AND is the desired outcome of the sorted groups. If I uploaded before resorting as noted, I apologize. However, it 'is' the 'reset' to put the data back to original format when playing with the code.

[JR869]

Thanks, SMC. However, I'm getting a 'Type mismatch' error on this line

cell(2).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=cell - 1 

This line I've had trouble understanding regardless...

JR

[JR869]

I made changes to the other 2 lines of code reflecting your reference above for Rows.Count, but it still gives a type mismatch. Watching the code in break, I end up with #VALUE errors in cells that are supposed to be the result of the R1C1 formula, stopping at the line referenced with the 'Type mismatch' error. I don't see anything wrong with the R1C1 code, but then again...I'm still wrapping my head around it.
JR