+ Reply to Thread
Results 1 to 3 of 3

Code for adding rows based on number

Hybrid View

ammartino44 Code for adding rows based on... 04-29-2014, 03:29 PM
Jack7774 Re: Code for adding rows... 04-29-2014, 04:26 PM
Jack7774 Re: Code for adding rows... 04-29-2014, 05:30 PM
  1. #1
    Forum Contributor
    Join Date
    08-14-2013
    Location
    LA Baby!!
    MS-Off Ver
    Excel 2007
    Posts
    1,598

    Code for adding rows based on number

    Hello. Could someone explain in detail what each piece of this code means/does? Thanks.

    Sub Onadd()
       Dim rng As Range
       Dim intRow As Integer
       With Range("Number")
          Set rng = .Cells(Rows.Count, 1).End(xlUp)
          intRow = 1
          Do Until intRow > rng.Row
             If IsNumeric(.Cells(intRow)) And .Cells(intRow).Value > 0 Then
                intRow = intRow + 1
                Rows(intRow & ":" & intRow + .Cells(intRow - 1).Value - 1).Insert
                .Cells(intRow) = 0
                intRow = intRow + .Cells(intRow - 1).Value - 1
             End If
             intRow = intRow + 1
          Loop
       End With
    End Sub

  2. #2
    Forum Contributor Jack7774's Avatar
    Join Date
    08-31-2012
    Location
    Georgia, US
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Code for adding rows based on number

    Sub Onadd()
    'Dim - Declares variables and (optionally) their data types. rng in this case is just what the user decided to name that as a range.
    'Range - is like a cell selection from a number of selected cells like A3:A8 however with Range its declared as a method not as a specific set of cells
       Dim rng As Range
    'Integer - A data type that holds integer variables stored as 2-byte whole numbers in the range -32,768 to 32,767. The Integer data type is also used to represent enumerated values. The percent sign (%) type-declaration character represents an Integer in Visual Basic.
       Dim intRow As Integer
    'With - Allows a shorthand way of accessing multiple properties for an object. Like this With Range selected do the next set of codes writen in that range specified in this case its "Number".
       With Range("Number")
    'this is setting up rng to be the last row number
          Set rng = .Cells(Rows.Count, 1).End(xlUp)
    'setting up intRow to = 1 since they setup intRow to be a number earlier in the macro
          intRow = 1
    'Do - this is a function that will do the code below it and Until is the a criteria that has to be met in order for the code to stop. As an entire line its saying do this until introw or 1 is greater than rng.row or last row. Do this from row 1 until the last row
          Do Until intRow > rng.Row
    'If is another function that calls one instance of code to take place if a condition is met. This code below seems to add +1 to intRow if the condition is met and then again +1 after even if the condition it met or not. So in some cases it will go up +1 if the condition is not met and when the condition is met it will go up +2 until the last row is reached. However in this case it will skip some of the rows and if this intended function is intended for every row then this code is broken technically. At least i think it is. I could be wrong. 
             If IsNumeric(.Cells(intRow)) And .Cells(intRow).Value > 0 Then
                intRow = intRow + 1
                Rows(intRow & ":" & intRow + .Cells(intRow - 1).Value - 1).Insert
                .Cells(intRow) = 0
                intRow = intRow + .Cells(intRow - 1).Value - 1
             End If
             intRow = intRow + 1
    'loop- is the same as end sub is for sub (). It is the end of the function Do so that the code will loop through once the end of the code is reached until condition of the Do is met then it will go past the Do.
          Loop
    'End with - is the same as end sub is for Sub (). It is the end of the function If so that the code will know the ending of of the function. On the If fuction you can also add in Else. If x then do this Else do this if condition is not met then End if. So that the code knows where to end or jump to to avoid running a line of code that is not necessary or intended to run.
       End With
    End Sub
    I hope that helps. I am not necessarily the best person to answer however I believe I have enough knowledge to explain it about 90% of it.
    Thank those who have helped you by clicking the Star * below their name and please mark your post [SOLVED] if it has been answered satisfactorily.

  3. #3
    Forum Contributor Jack7774's Avatar
    Join Date
    08-31-2012
    Location
    Georgia, US
    MS-Off Ver
    Excel 2010
    Posts
    576

    Re: Code for adding rows based on number

    I am glad it worked for you. Thanks for adding rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro code to insert 3 rows on spreadsheet with variable number of rows
    By D18GE in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2013, 07:33 AM
  2. [SOLVED] Need VBA code to count number of rows & split into group of 7 rows in same sheet
    By amy_d2 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-05-2013, 11:03 AM
  3. [SOLVED] Splitting a Cell based on "/" and adding new Rows based on Part Number Logic
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-30-2013, 10:11 AM
  4. Need VBA Code for Adding / Deleting Rows
    By sambuka in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-27-2012, 11:14 AM
  5. VBA code to unhide X number of rows based on a number entered into a cell above
    By gazza365 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-09-2010, 08:48 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1