Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Small Coding Problem for Excel Macro

  1. Jul 2, 2010 #1
    I want to code a macro that will take in an integer as a row number and insert two rows above that row. This will repeat in a for loop. The problem is that I'm not familiar with VB syntax. Here's what I have so far:

    Sub Insert_Rows_Loop()
    Dim CurrentSheet As Object
    Dim x As Integer

    ' Loop through all selected sheets.
    For Each CurrentSheet In ActiveWindow.SelectedSheets
    For x = 14 To 418
    ' Insert 2 rows depending on values of a1 and a2.
    CurrentSheet.Range("a" & x &:"a" & (x+1)&").EntireRow.Insert
    Next x
    Next CurrentSheet
    End Sub

    There is some sort of syntax error in the CurrentSheet.Range argument. How does one specify a range with a variable?

    Thanks.
     
  2. jcsd
  3. Jul 4, 2010 #2
    Try this...

    CurrentSheet.Range("a" & x & ":a" & (x+1)).EntireRow.Insert
     
  4. Jul 8, 2010 #3
    Thanks, but that just deleted everything :) I found another macro to use.
     
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook