Solved How to write Macro to add row to spreadsheet

April 29, 2015 at 08:48:55
Specs: Windows 7
I'm working with a very situation and need some input. I have a spreadsheet that if there if a value greater than 10 in the field, I need a row inserted below the current row.
Is there a macro someone can help me with to do this?
For example.. if G2>10, insert row beneath 2. All help is appreciated!!

See More: How to write Macro to add row to spreadsheet

Report •

#1
April 29, 2015 at 09:47:21
There are many different spreadsheet programs out there, and writing macros for them is not always done in the same way or with the same syntax. Letting us know what program you are using would make it easier to help.

Meanwhile lets hope one of the moderators can move this thread to the Office Software forum, where is belongs, and will attract attention from those best suited to help.

Nigel

Wind slow


Report •

#2
May 6, 2015 at 04:21:11
Here is something iv quickly put together.

question
How do you want this event to be triggered?
manually by running the code
or when someone enters a value on the sheet?

Assumptions
Seeing as you refered to column 'G' this code will also work only on column G

Sub InsertARow()
    
   Dim URange, LRange
   Dim BCell As Range
   
   Set URange = Range("G1")
   Set LRange = Range("G" & Rows.Count).End(xlUp)
   
   For Each BCell In Range(URange, LRange)
        If BCell.Value > 10 Then
            BCell.Offset(1, 0).EntireRow.Insert
        End If
    Next BCell
End Sub

You will need to manually run this code from Excel VBA editor


Report •

#3
May 6, 2015 at 07:39:12
✔ Best Answer
Nice code. I added an IF clause to allow the macro to be run multiple times.

As originally written, the code will insert a new row each time it is run and finds a value >10. You could end up with multiple new rows under existing values >10 each time it is run.

With the addition of the IF clause, the code will only insert rows where appropriate to ensure a single blank row below each >10 value. That way it can be re-run afer a new value is entered in Column G without doubling-up the existing blank rows.

I'll admit that I don't know if mrsjankowski wants multiple new rows under each >10 value or not, so I offer the modifed code as an option.



Sub InsertARow_rev1()
    
   Dim URange, LRange
   Dim BCell As Range
   
   Set URange = Range("G1")
   Set LRange = Range("G" & Rows.Count).End(xlUp)
   
   For Each BCell In Range(URange, LRange)
        If BCell.Value > 10 Then
            If BCell.Offset(1, 0) <> "" Then
                 BCell.Offset(1, 0).EntireRow.Insert
            End If
        End If
    Next BCell
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
May 7, 2015 at 01:51:12
Hi Derby,

Spot on, i didnt quite think of that and hadnt tested it with multiple runs. Thanks for updating the code.

Just a question, on my editor the '-rev1' is causing an 'Expected end of statement' error is this the same for you? iv had to ommit the hyphen - just thought id mention it in case it confuses the OP (if they decide to come back)

Sub InsertARow-rev1()

As always thanks for your valuable input

AWTL


Report •

Ask Question