Codes Explaination

Solved/Closed
waseem - Feb 25, 2011 at 10:32 AM
 Waseem - Feb 28, 2011 at 09:48 AM
Hello,

I have an text in excel as below.

1
1
1
2
2
2
3
3
3

After running a macro Iam getting the result as below.

1
1
1

2
2
2

3
3
3


Below are the codes used.


Sub Macro1()
Dim r As Long, mcol As String, i As Long

' find last used cell in Column A
r = Cells(Rows.Count, "A").End(xlUp).Row

' get value of last used cell in column A
mcol = Cells(r, 1).Text


' insert rows by looping from bottom
For i = r To 2 Step -1
If Cells(i, 1).Value <> mcol Then
mcol = Cells(i, 1).Value
Rows(i + 1).Insert
End If
Next i

End Sub

I got this from some website.Could someone please help me out with an codes explaination for eacha and every line.

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Feb 27, 2011 at 05:22 PM
The code is real basic. I dont think comments will help you much. here is bit more comments

' routine name is Macro1
Sub Macro1()

   
   'declaring variables r and i of type long and mcol of type string.
   'JUst a good practice also stop you from assigning string to variables r or i
   Dim r As Long, mcol As String, i As Long
   
   ' find last used cell in Column A. rows.count gives your number of rows on sheet
   ' basically it is like you go to last row of the sheet and press ctrl and up arrow at the
   ' same time. it will find the last used cell or row 1 if all is blank. that row number
   'is stored in variable r
   r = Cells(Rows.Count, "A").End(xlUp).Row
   
   ' get value of last used cell in column A or column 1 (same thing)
   mcol = Cells(r, 1).Text
   
   
   ' insert rows by looping from bottom
   ' this loop from what ever the value of "r" is , till 2. "i" stores the current loop value and initially starts with r and with each loop is subtracted -1 (step -1 part)
   For i = r To 2 Step -1
   
      ' checking if the cell at row "i" and column "A" is not equal to value mcol
      If Cells(i, 1).Value <> mcol Then
         
         ' the value is not equal to mcol
         
         ' assigning mcol as value of cell at row i and column A
         mcol = Cells(i, 1).Value
         
         ' insert a row after row i
         Rows(i + 1).Insert
      
      ' end of if condition
      End If
      
   
   Next i 'repeat loop
   'end of loop code
End Sub
0
Hi,

Thanks a lot it helps me a lot !!! Thanks for great help !!!
0