Not understanding my own VBA code..HELP!

[kamiba]

Hi guys,

I'm new here and very new to VBA (just know the bare basics) -- nice to meet you!

I wanted to create a code to insert a blank every 10th row and after copying and pasting bits and pieces of other people's codes, I finally got the code to work...but I'm not sure how this is working and would love to fully understand how this is actually working. I'm completely lost from the "i = 1" part (bolded below). What is the role of i and j, and why/how does the "Do" function play a role in this code? Thank you in advance for your help!



Sub InsertRowEvery10thRow()


Dim i As Long
Dim lr As Long
Dim j As Long


lr = Range("A" & Rows.Count).End(xlUp).Row


i = 1
j = 1

Do
If j = 11 Then
Rows(i).Insert Shift:=xlDown
j = 1

Else
j = j + 1
End If
i = i + 1

Loop While i <> lr

End Sub

 

[lrobbo314]

Maybe writing it like this will be a little more clear.

Sub EveryTenth()
Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
Dim rowNum As Long

For rowNum = 1 To LR
    If rowNum Mod 11 = 0 Then
        Rows(rowNum).Insert shift:=xlDown
    End If
Next rowNum

End Sub

The variable LR is set to the row number of the last cell in Column A that is not blank. Then the for loop is going from 1 to the variable LR. The variable rowNum increments from 1 to LR. If rowNum modulo 11 equals 0, i.e. every 11th row, the code will insert a new row.

Your code above is doing the same thing, just with more variables and using a 'Do' loop instead of a 'For' loop.

 

[The_Macrotect]

Hi Kamiba,

Welcome to the forum, and to VBA! Once you understand it, you'll be amazed by the things you can do with it! Let's take it one line at a time:

Sub InsertRowEvery10thRow()


Dim i As Long
Dim lr As Long
Dim j As Long


lr = Range("A" & Rows.Count).End(xlUp).Row <--- Counts the number of used rows in your sheet (only counts rows that contains data in column A)


i = 1 <--- declares that the variable "i" equals 1 (the variable "i" will change as the code runs)
j = 1 <--- declares that the variable "j" equals 1 (the variable "j" will change as the code runs)

Do
If j = 11 Then <--- this is a test: Does j=11?
Rows(i).Insert Shift:=xlDown <--- if it does, a new row will be inserted
j = 1 <--- and the variable "j" will return to 1 so that a new row will be inserted 10 rows later

Else <--- if j does NOT equal 11
j = j + 1 <--- add 1 to the variable "j"
End If
i = i + 1 <--- add 1 to the variable "i"

Loop While i <> lr <--- This code will loop while the variable "i" does NOT equal the variable "lr" (Once "i" equals "lr", it means the end of the sheet has been reached)

End Sub

In review:

The variable "lr" counts the number of used rows

The variable "i" is counting the number of rows

The variable "j" is counting every time 10 rows have passed

The "Loop While" statement keeps looping the code until all rows in your used portion of the sheet have been gone through. The loop goes back to the "Do" statement.

Let me know if something is unclear.


All the best,
Matt

 

[kamiba]

This makes much more sense...thank you so much for your help!

Maybe writing it like this will be a little more clear.

Sub EveryTenth()
Dim LR As Long: LR = Range("A" & Rows.Count).End(xlUp).Row
Dim rowNum As Long

For rowNum = 1 To LR
    If rowNum Mod 11 = 0 Then
        Rows(rowNum).Insert shift:=xlDown
    End If
Next rowNum

End Sub

The variable LR is set to the row number of the last cell in Column A that is not blank. Then the for loop is going from 1 to the variable LR. The variable rowNum increments from 1 to LR. If rowNum modulo 11 equals 0, i.e. every 11th row, the code will insert a new row.

Your code above is doing the same thing, just with more variables and using a 'Do' loop instead of a 'For' loop.

 

[kamiba]

Thank you so much for your help, Matt. Really appreciate your thorough explanation!

Just so I fully understand...why is Else j=j+1 necessary? I still can't wrap my head around this part...thank you in advance for your help!

Hi Kamiba,

Welcome to the forum, and to VBA! Once you understand it, you'll be amazed by the things you can do with it! Let's take it one line at a time:

Sub InsertRowEvery10thRow()


Dim i As Long
Dim lr As Long
Dim j As Long


lr = Range("A" & Rows.Count).End(xlUp).Row <--- Counts the number of used rows in your sheet (only counts rows that contains data in column A)


i = 1 <--- declares that the variable "i" equals 1 (the variable "i" will change as the code runs)
j = 1 <--- declares that the variable "j" equals 1 (the variable "j" will change as the code runs)

Do
If j = 11 Then <--- this is a test: Does j=11?
Rows(i).Insert Shift:=xlDown <--- if it does, a new row will be inserted
j = 1 <--- and the variable "j" will return to 1 so that a new row will be inserted 10 rows later

Else <--- if j does NOT equal 11
j = j + 1 <--- add 1 to the variable "j"
End If
i = i + 1 <--- add 1 to the variable "i"

Loop While i <> lr <--- This code will loop while the variable "i" does NOT equal the variable "lr" (Once "i" equals "lr", it means the end of the sheet has been reached)

End Sub

In review:

The variable "lr" counts the number of used rows

The variable "i" is counting the number of rows

The variable "j" is counting every time 10 rows have passed

The "Loop While" statement keeps looping the code until all rows in your used portion of the sheet have been gone through. The loop goes back to the "Do" statement.

Let me know if something is unclear.


All the best,
Matt

 

[The_Macrotect]

J keeps track of how many rows have been counted. The the first time the code runs, J=1. When it loops, 1 is added, making it 2, and so on until it gets to 11. Once J=11, a new row will be inserted and J will go back to 1 again so that 10 rows later, a new row will be inserted.

Matt

Thank you so much for your help, Matt. Really appreciate your thorough explanation!

Just so I fully understand...why is Else j=j+1 necessary? I still can't wrap my head around this part...thank you in advance for your help!