How to Use a Variable to Control the Number of New Rows Inserted
Results 1 to 6 of 6

Thread: How to Use a Variable to Control the Number of New Rows Inserted

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post How to Use a Variable to Control the Number of New Rows Inserted

    I'm using this line of code to add 12 empty rows at the top of a worksheet

    Var_RawData_Worksheet.Rows("1:12").Insert shift:=xlShiftDown


    I'd like to use a variable to control the number of rows but can't figure out the syntax, what I need is something like:

    VARIABLE_NO = 12

    Var_RawData_Worksheet.Rows("1: VARIABLE_NO").Insert shift:=xlShiftDown

    Apologies for what is probably a very stupid question, my VBA is coming on but sometimes the basic stuff beats me.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: How to Use a Variable to Control the Number of New Rows Inserted

    Where is the number of rows going to come from?
    - 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 gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,851
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to Use a Variable to Control the Number of New Rows Inserted

    do you mean:

    Code:
    Var_RawData_Worksheet.Rows("1:" &  VARIABLE_NO).Insert shift:=xlShiftDown

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


  4. #4
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Use a Variable to Control the Number of New Rows Inserted

    Quote Originally Posted by Fluff View Post
    Where is the number of rows going to come from?
    The variable value will be read in from a cell value

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: How to Use a Variable to Control the Number of New Rows Inserted

    How about something like
    Code:
    VARIABLE_NO = Sheets("Sheet1").Range("A1").Value
    Var_RawData_Worksheet.Rows(1).Resize(VARIABLE_NO).Insert shift:=xlShiftDown
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to Use a Variable to Control the Number of New Rows Inserted

    Quote Originally Posted by gallen View Post
    do you mean:

    Code:
    Var_RawData_Worksheet.Rows("1:" &  VARIABLE_NO).Insert shift:=xlShiftDown
    Yes, that works, thanks.

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
  •