For loop to create a top blank row

gripper

Board Regular
Hi Guys,

I am learning about nested loops but what I cannot determine is how to take values in a 2 dimensional array and through a nested loop move each row down 1 row and create a blank top row for new info to be inserted.

I will call this sub over in over from my main code I am working on to do this simple chore.

I have this practice code to populate the sheet.

Sub nestedLoopFor()
Dim r As Integer, c As Integer

For r = 1 To 20 'row number

'once row number is set it now goes to inter loop to process columns
For c = 1 To 10 'now it process across columns like a typewriter
'at the end it carriage return to next line (r)

Cells(r, c).Value = r & ", " & c 'This takes the coordinance of the current r
'and c and inserts the value requested.
Next c
Next r

MsgBox "Loop Completed"

End Sub


Now I am looking for a separate sub with a loop to move rows down by 1.

It must be a nested loop to handle the rows and columns separately like above

Thank you.
 

Fluff

MrExcel MVP, Moderator
How about
Code:
Sub gripper()
   Dim r As Long, c As Long
   
   For r = 21 To 2 Step -1
      For c = 1 To 10
         Cells(r, c) = Cells(r - 1, c)
         Cells(r - 1, c) = ""
      Next c
   Next r
End Sub
 
Last edited:

gripper

Board Regular
Fluff,

Thank you. That was a perfect solution which I can understand.

Thanks again

Gripper

How about
Code:
Sub gripper()
   Dim r As Long, c As Long
   
   For r = 21 To 2 Step -1
      For c = 1 To 10
         Cells(r, c) = Cells(r - 1, c)
         Cells(r - 1, c) = ""
      Next c
   Next r
End Sub
 

Peter_SSs

MrExcel MVP, Moderator
I understand that you are learning about nested loops and are therefore using that concept but of course that result could be obtained without any looping at all by moving all the cells at once.

Rich (BB code):
Sub gripper_v2()
  Range("A1:J20").Cut Destination:=Range("A2")
End Sub
Just another comment. If you are using the nested loops, the clearing of all the 'above' cells, except the very top row, is fairly pointless since those cells are going to get filled by what is above them in the next loop across that row above. So you could save a lot of those deletions and just do the top row at the end.

Rich (BB code):
Sub gripper_v3()
   Dim r As Long, c As Long
   
   For r = 21 To 2 Step -1
      For c = 1 To 10
         Cells(r, c) = Cells(r - 1, c)
         <del>Cells(r - 1, c) = ""</del>
      Next c
   Next r
   Range("A1:J1").ClearContents
End Sub
 

Some videos you may like

This Week's Hot Topics

Top