Add Rows and Titles

[JRR1229]

I am writing a very complex and long code to have a spreadsheet look like an old form that users are use to looking at. I am running into a problem with a "For I = 2 to n" portion of code. I use this loop in multiple spots. I'm only having an issue with this one loop. For some reason it is ending before "I" gets to "n." If you look at the image 1 I have attached. Row 3 was added because Column R information. Every time the information changes in Column R, the code is suppose to add a row like Row 3. I'm having issues with image 2 I have attached. It adds Row 5888, but then the loop ends. Even though it recognizes n as Row 5898 and I as 5889.

    'Sorts System Column
        For I = 3 To n
        
            b = Cells(I, 1).Row
            c = Cells(b, 1).End(xlDown).Row
            Range("A" & b, "S" & c).Sort key1:=Columns(18)
            
            
            n = EDRBIS.Cells(EDRBIS.Rows.Count, 14).End(xlUp).Row
            
            I = Cells(I, 1).End(xlDown).Row
        
        Next I

'Issue with this portion
    'Add System Title Row
        For I = 2 To n
        
            If IsEmpty(.Cells(I, 18)) Then
            
                Rows(I + 1).Insert
                Cells(I + 2, 18).Copy Destination:=.Cells(I + 1, 2)
                Range("A" & I + 1, "S" & I + 1).Interior.Color = RGB(211, 211, 211)
                
               I = I + 2

            ElseIf Cells(I, 18) <> Cells(I - 1, 18) Then
            
                Rows(I).Insert
                Cells(I + 1, 18).Copy Destination:=.Cells(I, 2)
                Range("A" & I, "S" & I).Interior.Color = RGB(211, 211, 211)
                
                I = I + 1
            
            End If
            
            n = EDRBIS.Cells(EDRBIS.Rows.Count, 14).End(xlUp).Row + 1
        
        Next I

 

[RobP]

Hi,

at the bottom of your code, when you reset "n" again here

 n = Cells(Rows.Count, 14).End(xlUp).Row + 1

why do ask it to fetch the last row of data and Add 1 to it ?

Normally once you've inserted a row, the data gets pushed down, and your n reset above will look for the last row automatically - so you can remove the "+ 1" from the end ?

hope it helps

Rob

 

[RobP]

Also, that line of code updates n regardless as to whether you insert a new row or not. So when you insert a new row, you redefine n (as your last row), BUT I hasn't been updated.

So, first time you set I from 2 to 10 lets say. n was set by your previous routine (lets say to 10 as thats the last row). So you loop through, and insert a row, pushing data downwards so now the last row is 11 not 10. But you don't update the "final I" from when you originally set it to 10. you increase it by 1 or 2 when you insert, but the loop still knows the max it has to go to is 10. Whereas we know the last item should now be 11.

So, in order to combat that - when you do a loop - start at the bottom and come backwards. (eg FOR I = n to 3 STEP -1)
that way, you are always inserting AFTER your row, and it doesn't affect your overal data range.

hope it helps

 

[JRR1229]

Hi,

at the bottom of your code, when you reset "n" again here

 n = Cells(Rows.Count, 14).End(xlUp).Row + 1

why do ask it to fetch the last row of data and Add 1 to it ?

Normally once you've inserted a row, the data gets pushed down, and your n reset above will look for the last row automatically - so you can remove the "+ 1" from the end ?

hope it helps

Rob

Rob,

Thank you for the response. When I run the code without fetching "n" at the bottom of the code, for some reason it doesn't update with added rows. It keeps "n" the same number of rows before anything has shifted down.

 

[RobP]

Hi, yes, you need to make your count go backwards ie. start from the last row as I mentioned in the previous comment. did you try it yet ?

(hard for me to test as only a portion of code.. but the loop should be backwards..)
cheers
Rob

n = EDRBIS.Cells(EDRBIS.Rows.Count, 14).End(xlUp).Row

 For I = n To 2 Step -1
       
            If IsEmpty(.Cells(I, 18)) Then
           
                Rows(I + 1).Insert
                Cells(I + 2, 18).Copy Destination:=.Cells(I + 1, 2)
                Range("A" & I + 1, "S" & I + 1).Interior.Color = RGB(211, 211, 211)
               
               I = I + 2

            ElseIf Cells(I, 18) <> Cells(I - 1, 18) Then
           
                Rows(I).Insert
                Cells(I + 1, 18).Copy Destination:=.Cells(I, 2)
                Range("A" & I, "S" & I).Interior.Color = RGB(211, 211, 211)
               
                I = I + 1
           
            End If
                   
        Next I

 

[JRR1229]

I can give you a dropbox link to the worksheet if you would like?

 

[JRR1229]

Hi, yes, you need to make your count go backwards ie. start from the last row as I mentioned in the previous comment. did you try it yet ?

(hard for me to test as only a portion of code.. but the loop should be backwards..)
cheers
Rob

n = EDRBIS.Cells(EDRBIS.Rows.Count, 14).End(xlUp).Row

 For I = n To 2 Step -1
      
            If IsEmpty(.Cells(I, 18)) Then
          
                Rows(I + 1).Insert
                Cells(I + 2, 18).Copy Destination:=.Cells(I + 1, 2)
                Range("A" & I + 1, "S" & I + 1).Interior.Color = RGB(211, 211, 211)
              
               I = I + 2

            ElseIf Cells(I, 18) <> Cells(I - 1, 18) Then
          
                Rows(I).Insert
                Cells(I + 1, 18).Copy Destination:=.Cells(I, 2)
                Range("A" & I, "S" & I).Interior.Color = RGB(211, 211, 211)
              
                I = I + 1
          
            End If
                  
        Next I

This did not work. It was adding the same title over and over.

 

[RobP]

Hi,

Indeed you have to put your mind into a mode where you are starting on the last row of your data, and now working backwards. What I am not sure about is exactly what your data looks like before you run the code, and what you would like to to look like after. I think I'm more or less there, but without seeing all the caveats in your data its a bit tricky (the XL2BB tool on here will allow you to upload part of your sheet for us to see much clearer if you can).

But in the absence of that, I'll share the below code with explanation:

I am unclear as to how often you are running this, eg, are you running this macro on what I'd call "processed data" - meaning it has to recognise rows you've added and coloured previously, or is it a one off on "fresh data with no coloured rows every time you run.."

For now, I am assuming you are running on data that looks like what you have on rows 5898 to 5890 above. eg. you've not inserted any rows yourself previously.

 For I = n To 2 Step -1
       
            If IsEmpty(Cells(I - 1, 18)) Then
           
                Rows(I).Insert
                Cells(I + 1, 18).Copy Destination:=Cells(I, 2)
                Range("A" & I, "S" & I).Interior.Color = RGB(211, 211, 211)
               
               'I = I + 2

            'ElseIf Cells(I, 18) <> Cells(I - 1, 18) Then
           
            '    Rows(I).Insert
            '    Cells(I + 1, 18).Copy Destination:=Cells(I, 2)
            '    Range("A" & I, "S" & I).Interior.Color = RGB(211, 211, 211)
               
            End If
                   
        Next I

So this code is going to start on your last row, and check for a blank cell row on the row above it in Col18. So it does nothing until it gets to Row 5891. Then it sees that 5890 is a blank. So it inserts a row on 5891 pushing your data down. Current row count (meaning "I") remains 5891. Then it copies your data from I+1 to this blank row, and colours it grey.

Then it will loop again to I becomes 5890 (eg your "Winter Enclosures" heading. The row above it is not blank, so it will loop again. etc.

Do you get the picture ?

Hope that helps, and if your data is different, or I'm on the wrong track, please don't hesitate to let us know

Rgds
Rob

 

[JRR1229]

That last code work Thank you

 

[RobP]

Thats great, thanks for the confirmation & feedback.

cheers
Rob