excel vba loop

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

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

Excel VBA - loop insert rows

resolvedResolved · Low Priority · Version 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:

Select only cell that contain text to lock format

For selecting cells that only contain Text in Excel

By selecting cells that only contain text, you can delete, fill or protect cells of this type.

Use short cut to Go to box (F5) or Edit, Go to
In the dialog box, click special button & select Constants and only check text or any other desired type.

Click OK.

And text cells will all be highlighted for you to apply format. Please note only works on one sheet at a time.

View all Excel hints and tips


Server loaded in 0.07 secs.