98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - loop insert rows
Excel VBA - loop insert rows
Resolved · 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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Select only cell that contain text to lock formatFor selecting cells that only contain Text in Excel |