Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel vba loop

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - loop insert rows

Excel VBA - loop insert rows

ResolvedVersion 2010

Jason has attended:
Excel Advanced course
Excel VBA Intro Intermediate course

Excel VBA - loop insert rows

Hi, I am very much new to VBA having been on a course last month.
I have a sheet of continuous data and I am trying to create a loop that inserts an empty row every time the text in column A changes. The loop should then move down and continue through until the last line of data.
The logic I used is that it looks to the cell above the active cell to see if if the value is different. If it is it inserts a row above the active cell and then moves down two rows; if it isn't it moves down one. This continues until the active cell reaches a blank cell.
----------------
'loop down column A until cell below <> value above it. then insert new row

Sheets("Rejected Time").Select
Range("A3").Select

Do Until ActiveCell.Value = ""

If ActiveCell.Value <> ActiveCell.Offset(-1, 0).Value Then

ActiveCell.EntireRow.Insert shift:=xlUp
ActiveCell.Offset(2, 0).Select

Else: ActiveCell.Offset(1, 0).Select

End If

Loop

---------------------

It does appear to work however I often get an error message stating that I have run out of memory. I think this may be because it is looping through the whole sheet rather than just through the data, and possibly this is because the positioning of the active cell may be incorrect?
I used a processs of trial an error in offsetting the active cell until it worked as it got quite confusing!

Many thanks, and please feel free to question in case I have missed anything out.

Jason

RE: Excel VBA - loop insert rows

Hi Jason

Thanks for getting in touch. I tried out your code on a makeshift worksheet and it seemed to work. I can't see any issues with it.

Have you tried stepping through the code using F8 to see when errors occur? This can bring problems to the surface in a more obvious way.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Excel VBA - loop insert rows

Thanks Gary, i didn't know about F8, that's a really useful tip.
The section of code above is part of a longer code, but using F8 showed that the steps worked ok through the whole thing.

I'm not sure what is causing the (intermittent) memory issues, maybe something to do with excel more generally, but good to know it is not the code.

Thanks for getting back so soon

Jason

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Concatenating Results of Formulas

To concatenate the results of formulas simply add the "&" after the formula or function closing bracket.

function1(....)&function2(.....)

see example Creating a range of monthly payments as text.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.1 secs.