Hi all,
How can I add 170 empty rows at every repeating 41th row for 40 sets? Was looking for a method to do that, only found methods to add one empty row at every 41th row, but I need to add 170 empty rows!
Ultimately, I would want to integrate this method into a macro.
Welcome to the board
Code below inserts 170 rows after row 40,80, etc
If there are headers in row 1 and the data starts in row 2, amend Rows(1 + r * 40) to Rows(2 + r * 40)
Please Login or Register to view this content.
Last edited by kev_; 09-07-2020 at 02:10 AM.
Click *Add Reputation to thank those who helped you. Ask if anything is not clear
Check out this 1 minute video clip of me showing you how Excel can help you out a great deal with regard to what you want to do:
https://drive.google.com/file/d/1Fh4...ew?usp=sharing
now, you can take the code that Excel generates for you (which you see at the end of that video - I deleted most of it, and just kept the relevant 6 lines), combine it with a little more to loop your rows and count them accordingly, and boom, you're done!
Please Login or Register to view this content.
Sorry, I am a total noob to vba. How do you insert these code?Originally Posted by kev_
Welcome to the board
Code below inserts 170 rows after row 40,80, etc
If there are headers in row 1 and the data starts in row 2, amend Rows(1 + r * 40) to Rows(2 + r * 40)
Please Login or Register to view this content.
Edit: i have figured how to get these to work. Thanks to all!
Last edited by isaachoo; 09-07-2020 at 02:44 AM.
(alt}{F11} is shortcut to go to VBA editor
(see menu) Insert \ Module \ paste code into the open window
(alt}{F11} is shortcut to go back to Excel
Thank you all again for the illuminating answer, I would like to close this thread since my query was answered. Admin pls help!
That is lazy
Click on FAQ - it is the first item
As posted by kev_:
Sub InsertRows()
Dim r As Long
For r = 40 To 1 Step -1
Rows(1 + r * 40).Resize(170).Insert
Next r
End Sub
May I know what each row and number means?
Sub InsertRows()
Tell VBA to expect variable r to be an integer
Dim r As Long
Loop with values of r starting at 40 counting down to 1
For r = 40 To 1 Step -1
when r is 40 insert 170 rows at row 1601 [= 1 + (40 X 40)]
When r is 39 insert 170 rows ar row 1561 [= 1 + (39 X 40)]
etc ...
Rows(1 + r * 40).Resize(170).Insert
Next r
End Sub
So if i put:
For r = 1 to 40 Step +1
It have similar effect?
Oh, i just realized that if i put R = 1 to 40, it does not work, as it will add 170 rows first to the 41th row, then everything will pushed down 1 row by 1 row.
So adding rows from the bottom will not have such an issue. I hope my idea can be conveyed clearly...
The code on post#2 was written looping backwards to prevent that issue
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks