Ttykuu

10 year ban after applying for a UK student visa

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

Hot air balloons as primitive bombers

Unfrosted light bulb

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

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

Exit shell with shortcut (not typing exit) that closes session properly

Jem'Hadar, something strange about their life expectancy

Can "few" be used as a subject? If so, what is the rule?

Have any astronauts/cosmonauts died in space?

What is the tangent at a sharp point on a curve?

Symbolism of 18 Journeyers

Should a narrator ever describe things based on a characters view instead of fact?

Is xar preinstalled on macOS?

Was World War I a war of liberals against authoritarians?

Determine voltage drop over 10G resistors with cheap multimeter

Should I be concerned about student access to a test bank?

What is it called when someone votes for an option that's not their first choice?

Hackerrank All Women's Codesprint 2019: Name the Product

Print last inputted byte

Animating wave motion in water

What (if any) is the reason to buy in small local stores?

How old is Nick Fury?

Homology of the fiber

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 4 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 4 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 4 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 4 mins ago

Jamal♦

30.4k11121227

asked 8 hours ago

a1a1a1a1a1a1a1a1a1a1

244

share|improve this question

edited 4 mins ago

Jamal♦

30.4k11121227

asked 8 hours ago

a1a1a1a1a1a1a1a1a1a1

244

edited 4 mins ago

Jamal♦

30.4k11121227

edited 4 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 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
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');

var $window = $(window),
onScroll = function(e)
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom))
StackExchange.using('gps', function() StackExchange.gps.track('embedded_signup_form.view', location: 'question_page' ); );
$window.unbind('scroll', onScroll);

;
$window.on('scroll', onScroll);

);

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

share|improve this answer

answered 7 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 7 hours ago

Bill HilemanBill Hileman

1766

answered 7 hours ago

Bill HilemanBill Hileman

1766

answered 7 hours ago

Bill HilemanBill Hileman

1766