Vwwxrf

Error in master's thesis, I do not know what to do

Interior of Set Notation

Do native speakers use "ultima" and "proxima" frequently in spoken English?

Why is participating in the European Parliamentary elections used as a threat?

Does fire aspect on a sword, destroy mob drops?

Emojional cryptic crossword

Print a physical multiplication table

TDE Master Key Rotation

is this saw blade faulty?

Norwegian Refugee travel document

How can an organ that provides biological immortality be unable to regenerate?

Is there any common country to visit for uk and schengen visa?

How to read string as hex number in bash?

Fair way to split coins

CLI: Get information Ubuntu releases

How do you justify more code being written by following clean code practices?

Extraneous elements in "Europe countries" list

Does the Shadow Magic sorcerer's Eyes of the Dark feature work on all Darkness spells or just his/her own?

Air travel with refrigerated insulin

Weird lines in Microsoft Word

"Marked down as someone wanting to sell shares." What does that mean?

Have the tides ever turned twice on any open problem?

Why is indicated airspeed rather than ground speed used during the takeoff roll?

Why I don't get the wanted width of tcbox?

Loop whose endpoint (?) change during the loop

Refer to other cells besides the one in the Cells.FindMatching between files and a list of filenames at scaleMaintaining row height while insertingShow all full/partial search matches in the first worksheet from other worksheetsVBA faster calculatingA loop that assembles an Excel sheet by assembling matches from other sheetsSlow VBA macro using nested loops and autofilter to consolidate select data from 2 worksheets into 1Housing a summary of information in a data fileSpeed up array loop vba ExcelOOP Cross Sum Solver

1

$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

share|improve this question

edited 2 mins ago

Jamal♦

30.4k11121227

asked 8 hours ago

a1a1a1a1a1a1a1a1a1a1

244

$endgroup$

add a comment | 

1

$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

share|improve this question

edited 2 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

share|improve this question

edited 2 mins ago

Jamal♦

30.4k11121227

asked 8 hours ago

a1a1a1a1a1a1a1a1a1a1

244

$endgroup$

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

share|improve this question

edited 2 mins ago

Jamal♦

30.4k11121227

asked 8 hours ago

a1a1a1a1a1a1a1a1a1a1

244

share|improve this question

edited 2 mins ago

Jamal♦

30.4k11121227

asked 8 hours ago

a1a1a1a1a1a1a1a1a1a1

244

edited 2 mins ago

Jamal♦

30.4k11121227

edited 2 mins ago

Jamal♦

30.4k11121227

asked 8 hours ago

a1a1a1a1a1a1a1a1a1a1

244

asked 8 hours ago

a1a1a1a1a1a1a1a1a1a1

244

1 Answer
1

active

oldest

votes

2

$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

share|improve this answer

answered 6 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
);



);

draft saved

draft discarded

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

Name

Email

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

Name

Email

Required, but never shown

2

$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

share|improve this answer

answered 6 hours ago

Bill HilemanBill Hileman

1766

$endgroup$

• 
  
  
  

  
  

  
  
  
  
  
  

  
  $begingroup$
  Thank you. I like yours better.
  $endgroup$
  – a1a1a1a1a1
  5 hours ago
  

  
  
  
  

add a comment | 

2

$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

share|improve this answer

answered 6 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

share|improve this answer

answered 6 hours ago

Bill HilemanBill Hileman

1766

$endgroup$

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

share|improve this answer

answered 6 hours ago

Bill HilemanBill Hileman

1766

answered 6 hours ago

Bill HilemanBill Hileman

1766

answered 6 hours ago

Bill HilemanBill Hileman

1766