Results 1 to 4 of 4

Thread: For loop to create a top blank row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Oct 2002
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default For loop to create a top blank row

    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.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,068
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    48 Thread(s)

    Default Re: For loop to create a top blank row

    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 by Fluff; Sep 14th, 2019 at 05:03 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Oct 2002
    Posts
    136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For loop to create a top blank row

    Fluff,

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

    Thanks again

    Gripper

    Quote Originally Posted by Fluff View Post
    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

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,357
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: For loop to create a top blank row

    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.

    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.

    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)
             Cells(r - 1, c) = ""
          Next c
       Next r
       Range("A1:J1").ClearContents
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •