Understanding VBA Code Using "TO" and "STEP"

Run the code line by line. I apologize in advance if I receive too basic information; however, other people may find useful information.

Sub SortNames()

      

        
        
        
      

    

This is the name of the procedure

Dim LR As Long

      

        
        
        
      

    

Creates a variable of type Long

to store the line number.

The Long

reason type is used here (as opposed to Integer

) because newer versions of Excel (2007 and later) have more rows (1,048,576) than can fit in Integer

. So you should always use Long

when getting line / count number in Excel or you might get an error when the numbers get large.

Application.ScreenUpdating = False

      

        
        
        
      

    

This is a good general VBA coding technique; it prevents the screen from flickering while the procedure is running, which provides a nicer user experience, and B. makes the code run faster. Note that as soon as the VBA routines have finished, ScreenUpdating will automatically turn on (otherwise you won't be able to interact with Excel anymore!).

Sheets("Stores").Select

      

        
        
        
      

    

Obviously: selects a sheet named "Stores". Due to the previous line, the ScreenUpdating

user will not see this sheet selected until the procedure is complete. I'm not sure why the author of the procedure decided to use it Select

here; I think it would be better to use a block With... End With

. Generally speaking, if you avoid using it Select

, it should be.

LR = Cells(Rows.Count, 1).End(xlUp).Row

      

        
        
        
      

    

The function Cells()

will return the object Range

related to ActiveSheet

(which is "Repository"). Cells(Rows.Count, 1)

returns a Range object for the most recent row in the first column (which is cell A1048576 in Excel 2007 or later). The method .End()

gets the last used cell in a row or column from some original location. .End(xlUp)

gets the last used cell in the column, starting at the bottom and going up. Therefore, this statement says:

"go to the very bottom of column A, then go up until you find the first used cell, and then tell me what the row number is and save it to LR

."

Parathetic: if a block were used With

as I suggested above, this line would be:

LR = .Cells(Rows.Count, 1).End(xlUp).Row

      

        
        
        
      

    

Note that the author of this procedure seems to know what they are doing; finding the last used cell to start with and not starting from the very last cell in the whole book is a very good idea. Otherwise, for the rest of the procedure, Excel will delete every blank line in the workbook, for no reason! Besides being a waste of computing power (meh who cares, right?), It will take more... Starting with the last line used in this way is a very good technique for faster procedures.

For i = LR To 2 Step -1

      

        
        
        
      

    

This loop For

starts with LR

( For i = LR

), executes the body of the loop, and then subtracts 1 from i

( Step -1

meaning "subtract 1 from i

for each step") and loops again. It does this until it i

equals 2 ( To 2

). Note that on the last execution of the loop i

= 2 (it does not skip 2, the operator is To

on).

 'Here is where you can add the names

      

        
        
        
      

    

This is just a comment.

 If Range("f" & i) <> "Amanda Weaver" And Range("f" & i) <> "Debra Wiesemann" And _
   Range("f" & i) <> "Drew Simpson" And Range("f" & i) <> "James Howard" And _
   Range("f" & i) <> "Jeff Hruby" And Range("f" & i) <> "Jessica Czupryn" And _
   Range("f" & i) <> "Kevin Janke" And Range("f" & i) <> "Matthew Hudspeath" And _
   Range("f" & i) <> "Maurey Peterson" And Range("f" & i) <> "Noah Hadro" And _
   Range("f" & i) <> "Thomas McHenry" And Range("f" & i) <> "Thomas McHenry" Then

      

        
        
        
      

    

This is a rather large operator If

that checks a condition or conditions, and Then

executes some code if it is a condition True

. You can probably guess what the instruction is doing here If

, but I'll explain anyway.

The operator checks if a value is Range("f & i) <> "*some name*"

relevant True

. Range("f" & i)

returns an object Range

in ActiveSheet

with the cell address of column F, and a string i

(remember it i

starts with LR

and then counts down to 2). A part <>

is an operator that simply means not equal. Parts And

are a different operator; it just evaluates several conditions at the same time. Therefore, if the BOTH conditions on both sides of a statement And

are equal True

, the entire statement evaluates to true. If one or both are False

, you get a lie. When you put And

them together, they ALL have to be True

in order to receive True

. So the first time through the loop For

if the value in the stringLR

, column F is not equal to any of the names in the list, then the body of the statement will be executed If

.

Another explanation as to what exactly is happening here: VBA does some things automatically-magically to make things a little easier. One of these things is the interpretation of what you wanted to do, although it doesn't really make sense. If you think about it, there is no point in comparing an object Range

to a string like "Amanda Weaver". An object is Range

much more than just the string value it contains; here is a small list of things that are part Range

other than its meaning: formula, name, background color, text color, text formatting, address, parent, row and column, and many other things. However, VBA assumes that when you say IF Range(F100) <> "Amanda Weaver"

you want to compare the value cell F100 with "Amanda Weaver" and not with other parts.

  Range("f" & i).EntireRow.Delete Shift:=xlUp

      

        
        
        
      

    

This is the body of the block If

. Everything that happened here will happen if your test condition is higher than True

.

The part Range("f" & i)

was described earlier. Part EntireRow.Delete

means deleting the entire row that this object belongs to Range

(remember: it only removes the row if the value in column F does not contain any of the names in the list). The last bit,, Shift:=xlUp

tells Excel how to delete a row. This means that all cells under the deleted cell will be shifted up (as opposed to all cells to the right of the deleted cell shifted to the left).

 End If

      

        
        
        
      

    

Duh - This completes your block If

.

Next i

      

        
        
        
      

    

Remember that all this time we have been inside this cycle For

. Everything from For

up to that point will run again, except that it i

is equal to a value of 1 less than the most recent value (until it is equal to 2, inclusive).

Application.ScreenUpdating = True

      

        
        
        
      

    

As I explained above, this line is probably unnecessary. However, this is not a bad thing, and it is good practice to get in the habit of explicitly saying what you intend to do in your code, even if it happens automatically, for two reasons:

• Later when you go back and look at your code, you will forget what you meant. Seriously, you totally. Don't argue.
• When other people use your code, you cannot assume that they know everything they know. Therefore, make it as obvious as possible what you intend to accomplish.

_

End Sub

      

        
        
        
      

    

This completes the procedure. Yay.

How to learn VBA

Learning VBA can be frustrating (it was for me), and finding your own answers can often be very enjoyable. With this in mind, let me offer some advice.

I found a good place for information on the meaning of unfamiliar VBA code - MSDN; however actually finding something on MSDN can be a real trick, so I google it. Good search technique: "There is unknown MSDN VBA code here."

Here is an MSDN page describing the loop structure For To Next

.

Anytime you run a feature or keyword that you haven't seen yet, take a moment to review it and read about it. I found this to be one of the most effective teaching methods.