For loop to create a top blank row

gripper

Board Regular
Joined
Oct 29, 2002
Messages
176
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top