How to insert blank row below a cell containing specific text
Good Afternoon Friends,
I was wondering if someone might be able to assist me with using VB to insert a new row below a cell containing specific text.
For example:
- All of my data is in column A
-I want to scan all of column A, and if there is a cell that contains "ACHCAMERIGROUP M", then I want a blank row inserted below it. If column A does NOT contain that text....do nothing.
Thanks so much in advance,
Laura
Last edited by Lmsloman; 10-19-2009 at 04:42 PM. Reason: Problem Solved :)
Hows this do for ya?
Please Login or Register to view this content.
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
You get a gold star for the day!
Thank you so much!
If there's only one instance of this value, there's no need to loop, you can jump directly to it:
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Unfortunately, I don't know how many instances of this there would be.
But hey, if I wanted to get extra fancy....is there a way to specify to change the newly created blank cell in column A to "NA" or any other specific text?
Do this instead, its better than the other code anyway as I may have missed some values near the end.
Please Login or Register to view this content.
Hi,
Regarding to the helpfull code provide by DaveGugg, how to change below code to have a row insert ABOVE te value indicated and not BELOW
I have searched, tried almost all day now but didn't succeed. Nevertheless I expect the solution to be fairly simple.
Ferdinand
- - - - - - - -
Please Login or Register to view this content.
Last edited by arlu1201; 05-15-2012 at 04:12 AM. Reason: Please use code tags in future.
Change the code to insert at "a - 1" instead of "a + 1".
Hi,
Thanks for the quick reply, but unfortunately it didn't work.
1st attempt --> changed both a + 1 into a - 1
result: ca. 6000 lines added and one line inserted one row below the needed row (caused by the a-1 ?)
2nd attempt --> changed the final a +1 into a - 1 in the code
result: excel is adding empty rows from the row with correct argument untill limit of file is reached.
Re: How to insert blank row below a cell containing specific text
So, maybe a new code?
If this would be my column in excel ....
A
A
A
B
A
A
A
... i want to have an empy row inserted above each B
In other wors, the command 'insert row' should be made in the row where the B is located.
However, I have the feeling that after inserting one row (above) the found value 'B', the new value 'B' will be the one previous found and thus adding loads of empty rows.
So, after adding the row, the code should be programmed that it skips an extra row to continue the search without bouncing on the same argument again.
Does that makes sence?
Ferdinand.
Give this a try:
Please Login or Register to view this content.
Thanks for the help. It is working now, and quite fancy I must say.
'please select string' and 'please select row'. Superb!
Thanks again.
Ferdinand
Last edited by Ferdinand; 08-18-2010 at 04:22 AM.
Ferdinand, I just noticed this was someone else's thread. Next time be sure to start a thread of your own, as per the Forum Rules. Thanks.
could any one help me how to insert a new row between two bold cell values. ex: insert a new row if values of b1 and b2 are bold and this implies to the whole row.
Hemaveera,
Welcome to the Forum, unfortunately:
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks