LinkBack URL
About LinkBacks
Hello everyone
In the following code
it is supposed to insert one empty row between four rows ..
Please Login or Register to view this content.
Need to be able to insert 10 rows instead of one empty row
Thanks advanced for help
Hello everyone
In the following code
it is supposed to insert one empty row between four rows ..Please Login or Register to view this content.
Need to be able to insert 10 rows instead of one empty row
Thanks advanced for help
< ----- Please click the little star * next to add reputation if my post helps you
Visit Forum : From Here
I found this code but didn't get exact results too
I have data starting in row 1 till 1000 (for example)Please Login or Register to view this content.
Need to insert 10 blank rows between each four rows
Try
Please Login or Register to view this content.
Last edited by jason.b75; 05-21-2016 at 07:02 AM.
Now I have solved most of it using this code
It works great but the problem is that it begins from the last row so I got groups of four rows of data and 10 empty rows in between ,, but the first group in the first rows is only of two so i need a way to do the task starting from the first row not the last rowPlease Login or Register to view this content.
Please Login or Register to view this content.
Thanks Mr. Jason for reply
I think it would be better to attach a sample workbook to illustrate my request clearly
In Columns F:G I attached expected output
@Mr. AB33 it doesn't work well for me
Had a slight error in my code, the Step should be 14, not 15 (4 rows of data + 10 blank rows per group).
I've edited my previous reply to correct the code.
Inserting rows should be best done using backward loops, not forward.
Great Mr. Jason I have already tested it .. Look carefully at the results .. it worked for two groups only then nothing happened
I think this number 1.25 would be changed but don't know what it refers to
Great this is in case we don't know the circumstances .. I need to deal with fixed empty rows and fixed number of rows for each group ..Originally Posted by AB33
In this code
I changed 1.25 to 10 and now it is working very well as expectedPlease Login or Register to view this content.
But need to know what 1.25 refers to .. it is some of manual calculation
Last edited by YasserKhalil; 05-21-2016 at 07:18 AM.
Oops, I missed that. It's caused by the multiplier used to correct the last row once you start inserting blanks.
Here's a different approach that doesn't fall foul of that problem.
I've set the code so that you can change the pattern by adjusting the values of the 3 variables based on the comments in the code.Please Login or Register to view this content.
That's great Mr. Jason
Thank you very much for this wonderful solution
The code in Post # 11 works too but I need to know what 1.25 refers to and how to automate this number to suit the process of insert?
This is my working try too
Please Login or Register to view this content.
1.25 is an adjustment to correct the position of the last row.
If you start with 50 rows of data, the line of code that identifies the last row will only work up to row 50. As you insert blank rows, the last row moves down, but the code still works with the original last row.
To allow for this you need to predict where the last row will be by calculating the difference between the original number of rows and the number of rows you will have once the code is finished.
In your original code, you had 4 rows of data, then insert 1 blank row, giving you a total of 5 rows per group. 5/4 = 1.25, which is where the adjustment value comes from.
With the revised code, it would be 4 rows of data, insert 10 blank rows, total of 14 rows per group, so the adjustment should be 14/4 = 3.25.
In reality, any number greater than 3.25 would work, which is why your code runs fine when you changed it to 10, it just means that you're running the code on empty rows below the end of the data, effectively bloating the size of your sheet.
edit:-
Looking at your code in post #14, I think that you have understood how it works
I am shocked ..!
After trying to apply codes to original file (10,000 rows) the code took about 25 seconds to execute
Is there a possible way to get it faster .............??
You could try setting calculation to manual while the code runs, but to be fair, I think that 25 seconds sounds reasonable for 10,000 rows.
Thanks for reply and for post # 15 .. it is great explanation and I changed this line according to your explanation
As an idea but I don't know if it is possible or not..Please Login or Register to view this content.
Is it possible to store the rows where the insertion will be and insert empty row in one shot!! I think I am crazy related to this idea
The theory sounds possible, I've made it work for inserting single rows.
But as soon as you try to resize to insert multiple rows it fails.Please Login or Register to view this content.
I'm going to be offline now for the rest of the day, hopefully someone else will pick up on the thread, but if not, I will have another look later to see if I can figure something out.
I suggest you to reduce your "shocking state", it is not good for your heart.
Please Login or Register to view this content.
Thank you very much for this great step ..
I think it is a good start to the main problem
Have a nice day and I will wait for you
Mr. Karedog
No No No I am shocked again ... Really shocked of that wonderful and fascinating solution ..
With your solutions I feel very happy to great extent
Now it is completely solved in perfect and very speedy way
You are ROCK
Thank you very very much and best regards
Please Can you have a look at this thread as it is related somewhat to this issue
http://chandoo.org/forum/threads/cop...fic-way.29562/
Last edited by YasserKhalil; 05-21-2016 at 09:35 AM.
Eh, another shocking state ? Sorry about that.
You are welcome, and, I am a human, not a rock.
Excel Forum is the only forum I have account, so I can't download the attachment, hope someone else can help you there.
Regards
Thanks a lot Mr. Karedog for reply
I mean with ROCK not the ROCK itself but it has another meaning which is (you rock. You are really great.)
Thanks a lot for great helpyou rock
colloq. phr. - expression used to convey admiration, gratitude, and/or affection. Often appears in conversations when one has impressed the other; whether it be by a show of one's talent or skill, or generosity and kindness at heart. Also effectively used in casual conversation as informal expression of love, especially when in the presence of others.
After a particularly "attentive" evening of lovemaking, the next morning Laura said to Chris, "You rock", while amongst their group of friends, though she was really saying "I love you so much and you are the MAN"
May I post the same issue here again and refer to the original thread at chandoo?
I have posted it here again ..
http://www.excelforum.com/showthread...68#post4392568
Last edited by YasserKhalil; 05-21-2016 at 10:39 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks