Loop whose endpoint (?) change during the loop
$begingroup$
I have a loop which insert rows on specific cases. The loop goes through ~2000 rows and insert about 500.
Due to the inserted rows it doesn't loop the full range I want. I have solved that problem by counting the number of row I will insert with a first loop and then looping up to LastRow + Counter.
I feel as if there must have been a better way to do it.
Sub AddRows()
Dim i As Integer: i = 8
Dim LastRow As Integer: LastRow = Cells(8, 2).End(xlDown).Row
Dim counter As Integer: counter = 0
For i = 8 To LastRow
If Cells(i, 1) <> "" Then
counter = counter + 1
End If
Next i
NewLastRowAfterAddingRows = LastRow + counter
For i = 8 To NewLastRowAfterAddingRows
If Cells(i, 1) <> "" Then
Range(i + 1 & ":" & i + 1).Insert CopyOrigin:=xlFormatFromRightOrBelow
i = i + 1
End If
Next i
End Sub
vba excel
edited 11 mins ago
Jamal♦
30.4k11121227
asked 8 hours ago
a1a1a1a1a1a1a1a1a1a1
244
$endgroup$
add a comment |
$begingroup$
I have a loop which insert rows on specific cases. The loop goes through ~2000 rows and insert about 500.
Due to the inserted rows it doesn't loop the full range I want. I have solved that problem by counting the number of row I will insert with a first loop and then looping up to LastRow + Counter.
I feel as if there must have been a better way to do it.
Sub AddRows()
Dim i As Integer: i = 8
Dim LastRow As Integer: LastRow = Cells(8, 2).End(xlDown).Row
Dim counter As Integer: counter = 0
For i = 8 To LastRow
If Cells(i, 1) <> "" Then
counter = counter + 1
End If
Next i
NewLastRowAfterAddingRows = LastRow + counter
For i = 8 To NewLastRowAfterAddingRows
If Cells(i, 1) <> "" Then
Range(i + 1 & ":" & i + 1).Insert CopyOrigin:=xlFormatFromRightOrBelow
i = i + 1
End If
Next i
End Sub
vba excel
edited 11 mins ago
Jamal♦
30.4k11121227
asked 8 hours ago
a1a1a1a1a1a1a1a1a1a1
244
$endgroup$
add a comment |
$begingroup$
I have a loop which insert rows on specific cases. The loop goes through ~2000 rows and insert about 500.
Due to the inserted rows it doesn't loop the full range I want. I have solved that problem by counting the number of row I will insert with a first loop and then looping up to LastRow + Counter.
I feel as if there must have been a better way to do it.
Sub AddRows()
Dim i As Integer: i = 8
Dim LastRow As Integer: LastRow = Cells(8, 2).End(xlDown).Row
Dim counter As Integer: counter = 0
For i = 8 To LastRow
If Cells(i, 1) <> "" Then
counter = counter + 1
End If
Next i
NewLastRowAfterAddingRows = LastRow + counter
For i = 8 To NewLastRowAfterAddingRows
If Cells(i, 1) <> "" Then
Range(i + 1 & ":" & i + 1).Insert CopyOrigin:=xlFormatFromRightOrBelow
i = i + 1
End If
Next i
End Sub
vba excel
edited 11 mins ago
Jamal♦
30.4k11121227
asked 8 hours ago
a1a1a1a1a1a1a1a1a1a1
244
$endgroup$
I have a loop which insert rows on specific cases. The loop goes through ~2000 rows and insert about 500.
Due to the inserted rows it doesn't loop the full range I want. I have solved that problem by counting the number of row I will insert with a first loop and then looping up to LastRow + Counter.
I feel as if there must have been a better way to do it.
Sub AddRows()
Dim i As Integer: i = 8
Dim LastRow As Integer: LastRow = Cells(8, 2).End(xlDown).Row
Dim counter As Integer: counter = 0
For i = 8 To LastRow
If Cells(i, 1) <> "" Then
counter = counter + 1
End If
Next i
NewLastRowAfterAddingRows = LastRow + counter
For i = 8 To NewLastRowAfterAddingRows
If Cells(i, 1) <> "" Then
Range(i + 1 & ":" & i + 1).Insert CopyOrigin:=xlFormatFromRightOrBelow
i = i + 1
End If
Next i
End Sub
vba excel
vba excel
edited 11 mins ago
Jamal♦
30.4k11121227
asked 8 hours ago
a1a1a1a1a1a1a1a1a1a1
244
edited 11 mins ago
Jamal♦
30.4k11121227
asked 8 hours ago
a1a1a1a1a1a1a1a1a1a1
244
edited 11 mins ago
Jamal♦
30.4k11121227
edited 11 mins ago
Jamal♦
30.4k11121227
edited 11 mins ago
Jamal♦
30.4k11121227
30.4k11121227
asked 8 hours ago
a1a1a1a1a1a1a1a1a1a1
244
asked 8 hours ago
a1a1a1a1a1a1a1a1a1a1
244
asked 8 hours ago
a1a1a1a1a1a1a1a1a1a1
244
244
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
$begingroup$
It's generally not a good idea to alter the iterator variable in a for/next loop. While your code works, it's just not a good practice. Also, your initializing i to 8 is redundant since your loop starts at 8 as well. Here's how I would handle it:
Sub AddRows()
Dim i As Integer
Dim LastRow As Integer
Dim counter As Integer
LastRow = Cells(8, 2).End(xlDown).Row
counter = 8
For i = counter To LastRow
If Cells(counter, 1) <> "" Then
counter = counter + 1
Range(counter & ":" & counter).Insert CopyOrigin:=xlFormatFromRightOrBelow
End If
counter = counter + 1
Next i
End Sub
answered 7 hours ago
Bill HilemanBill Hileman
1766
$endgroup$
$begingroup$
Thank you. I like yours better.
$endgroup$
– a1a1a1a1a1
5 hours ago
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
return StackExchange.using("mathjaxEditing", function () {
StackExchange.MarkdownEditor.creationCallbacks.add(function (editor, postfix) {
StackExchange.mathjaxEditing.prepareWmdForMathJax(editor, postfix, [["\$", "\$"]]);
});
});
}, "mathjax-editing");
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "196"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f215693%2floop-whose-endpoint-change-during-the-loop%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
$begingroup$
It's generally not a good idea to alter the iterator variable in a for/next loop. While your code works, it's just not a good practice. Also, your initializing i to 8 is redundant since your loop starts at 8 as well. Here's how I would handle it:
Sub AddRows()
Dim i As Integer
Dim LastRow As Integer
Dim counter As Integer
LastRow = Cells(8, 2).End(xlDown).Row
counter = 8
For i = counter To LastRow
If Cells(counter, 1) <> "" Then
counter = counter + 1
Range(counter & ":" & counter).Insert CopyOrigin:=xlFormatFromRightOrBelow
End If
counter = counter + 1
Next i
End Sub
answered 7 hours ago
Bill HilemanBill Hileman
1766
$endgroup$
$begingroup$
Thank you. I like yours better.
$endgroup$
– a1a1a1a1a1
5 hours ago
add a comment |
$begingroup$
It's generally not a good idea to alter the iterator variable in a for/next loop. While your code works, it's just not a good practice. Also, your initializing i to 8 is redundant since your loop starts at 8 as well. Here's how I would handle it:
Sub AddRows()
Dim i As Integer
Dim LastRow As Integer
Dim counter As Integer
LastRow = Cells(8, 2).End(xlDown).Row
counter = 8
For i = counter To LastRow
If Cells(counter, 1) <> "" Then
counter = counter + 1
Range(counter & ":" & counter).Insert CopyOrigin:=xlFormatFromRightOrBelow
End If
counter = counter + 1
Next i
End Sub
answered 7 hours ago
Bill HilemanBill Hileman
1766
$endgroup$
$begingroup$
Thank you. I like yours better.
$endgroup$
– a1a1a1a1a1
5 hours ago
add a comment |
$begingroup$
It's generally not a good idea to alter the iterator variable in a for/next loop. While your code works, it's just not a good practice. Also, your initializing i to 8 is redundant since your loop starts at 8 as well. Here's how I would handle it:
Sub AddRows()
Dim i As Integer
Dim LastRow As Integer
Dim counter As Integer
LastRow = Cells(8, 2).End(xlDown).Row
counter = 8
For i = counter To LastRow
If Cells(counter, 1) <> "" Then
counter = counter + 1
Range(counter & ":" & counter).Insert CopyOrigin:=xlFormatFromRightOrBelow
End If
counter = counter + 1
Next i
End Sub
answered 7 hours ago
Bill HilemanBill Hileman
1766
$endgroup$
It's generally not a good idea to alter the iterator variable in a for/next loop. While your code works, it's just not a good practice. Also, your initializing i to 8 is redundant since your loop starts at 8 as well. Here's how I would handle it:
Sub AddRows()
Dim i As Integer
Dim LastRow As Integer
Dim counter As Integer
LastRow = Cells(8, 2).End(xlDown).Row
counter = 8
For i = counter To LastRow
If Cells(counter, 1) <> "" Then
counter = counter + 1
Range(counter & ":" & counter).Insert CopyOrigin:=xlFormatFromRightOrBelow
End If
counter = counter + 1
Next i
End Sub
answered 7 hours ago
Bill HilemanBill Hileman
1766
answered 7 hours ago
Bill HilemanBill Hileman
1766
answered 7 hours ago
Bill HilemanBill Hileman
1766
answered 7 hours ago
Bill HilemanBill Hileman
1766
1766
$begingroup$
Thank you. I like yours better.
$endgroup$
– a1a1a1a1a1
5 hours ago
add a comment |
$begingroup$
Thank you. I like yours better.
$endgroup$
– a1a1a1a1a1
5 hours ago
$begingroup$
Thank you. I like yours better.
$endgroup$
– a1a1a1a1a1
5 hours ago
$begingroup$
Thank you. I like yours better.
$endgroup$
– a1a1a1a1a1
5 hours ago
add a comment |
Thanks for contributing an answer to Code Review Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
Use MathJax to format equations. MathJax reference.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fcodereview.stackexchange.com%2fquestions%2f215693%2floop-whose-endpoint-change-during-the-loop%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
