Microsoft Excel Power
Lesson 2: The Basics of VBA
Dimensions of your Worksheet
Often in your code you have to know the dimensions of the data in the worksheet you are dealing with. So, how do we determine this?
Well, we know the first cell in any worksheet (generally) is "A1", the top left, but what about the last cell, the bottom right?
Here is a simple bit of code to determine this:
Dim rng As Range Dim lLastRow As Integer Dim lLastCol As IntegerSet rng = Range("A1").SpecialCells(xlCellTypeLastCell) lLastRow = rng.Row lLastCol = rng.Column
We define the appropriate variables; rng will hold our range of cells and so is of type Range, and lLastRow and lLastCol, Integers, will hold the values of the last row and last column. This technique uses a property of the range object that determines the dimensions of the data in the worksheet. We then use the rng.Row, and rng.Col to get these values. After this bit of code is executed we will know how many rows we have in worksheet and how many columns.
I use this in almost all of my macros.
Armed with this information we can set about the writing of the macro to perform the required tasks.
Run the macro, Dimension, associated with tab {Dimensions} in the spreadsheet. You should get the correct answer for the last row and col.
You can also reference cells using the Cells(row, col) option, where row and col are values representing the specific cell you want. For example, cell "A1" would be represented as
Cells(1,1)
There are a number of ways to represent most things in VBA, the choice generally depends on what is most convenient for your requirements.
Searching for a value
Very often in your code you need to search for a value before you do something such as delete the row or change the fill color to highlight the row or column. This will generally involve looping through all of the rows or columns in your data.
To loop through all of the rows: First you need to know the dimensions of the data; we can use the statements above to get us that, then we can just use a simple For loop. Run the macro LoopData in tab {Loop} to see how the code works.
Here is the code used.
Our variables
Dim rng As Range Dim lLastRow As Integer Dim lLastCol As Integer Dim i As Integer Dim SearchValue As String
Here we set the value to search for; normally this would be obtained from the user and not set as a value in the code
SearchValue = "Xk303" ' Set search value
Now we get the dimensions of the data
Set rng = Worksheets("Loop").Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = rng.Row
lLastCol = rng.Column
Now for the loop
For i = 5 To lLastRow If Cells(i, "A").Value = SearchValue Then MsgBox "Search value found at Row: " & i End If Next i
Here we use the For loop structure. Notice that I start at row 5; this is because I don't need to search the headings, and I stop at lLastRow, which is the last row of data. I'm using an If statement to test for the value I want, and use the Cells option. In each loop of the search, the value of i is incremented by 1 from the Next i statement, and is used in the Cells statement to reference the particular row. Because I know what column the value I'm looking for is in I can use "A" for the column value in the Cells statement. If I wanted to vary the column that I searched in then I would use another variable such as j, and have that value changed by another For loop. We will see an example of this later on.
When you run the macro it will display a MsgBox with the row value containing our search value. Notice also that I prefix the range statement with:
Worksheets("Loop")
This is to ensure that the macro works on the correct Sheet, the default, unless specified, is the current active sheet. Generally this is not a problem, but as we will see later when using two sheets in a macro, you sometimes have to explicitly specify the sheet.
In this next example we will search through the data looking for a particular value and then delete the row it is in. An important point about deleting rows and columns - always delete rows from the bottom up and columns from right to left. This is because, as you delete a row or column, Excel will renumber the rows and alter the letters of the columns; this can mess up your code if you go the other way! You will see in a later lesson how important this is. The LoopDelete macro in the {Loop} tab shows this process.
When you run the code it will find rows that have the value "QQP" in column "C" and delete them, at the end of the macro it will display the number of rows deleted.
Here are the important points of the code:
Variables
Dim rng As Range Dim lLastRow As Integer Dim lLastCol As Integer Dim i As Integer Dim DeleteCount As Integer Dim SearchValue As String' Application.ScreenUpdating = False
SearchValue = "QQP" ' Set search value DeleteCount = 0
Here we see that the Searchvalue is set to "QQP", again this would not normally be done this way unless you always wanted to delete rows based upon this value. I also set the DeleteCount variable to 0; VBA will initialize most variables for you, but don't get in the habit of relying on this as it can cause problems. Also, when you run the macro you will notice some screen flicker as the rows are deleted. This is not a problem with this little amount of data but for bigger amounts it can get really annoying. To shut that off, uncomment the Application.ScreenUpdating = False Statement and you will not see this. Now the downside to suppressing the screen updates is that people running a macro can think that it is doing nothing, so you can use the Application.StatusBar option to display messages in the status bar at the bottom left hand corner of Excel.
Here we get the dimensions of the data.
Set rng = Worksheets("Loop").Range("A1").SpecialCells(xlCellTypeLastCell)
lLastRow = rng.Row
lLastCol = rng.Column
And now for the delete loop
For i = lLastRow To 5 Step -1 If Cells(i, "C").Value = SearchValue Then Cells(i, "C").EntireRow.Delete DeleteCount = DeleteCount + 1 End If Next i Application.StatusBar = "Number of rows deleted = " & DeleteCount
Much the same as before except instead of a MsgBox display we use the EntireRow.Delete command. Now, "how did I know this command?" you may ask yourself; well maybe I'm just that good! Or perhaps what I did was to use the macro recorder. Here is how I went about it. The steps for the problem were as follows:
- Find a row with the specific value I'm looking for
- Delete that row
Not a very difficult problem, but it illustrates the process. The search part cannot be recorded so you have to create a loop as shown, but the deletion of a row can be recorded. To see this, go to the {Loop} tab and select record a macro. Call it "DeleteRow" and store it in "This Workbook." Now select any cell on the row you want to delete and then delete the row (either from Edit->Delete, or using the little quick menu). Now stop the recording. Depending on whether you are in the same session or not, Excel will place this code in Module1 or possibly Module2. When you find the code it will look something like this:
Range("A29").Select
Selection.EntireRow.Delete
You can see that I selected cell "A29" and then with that selection the macro used the
EntireRow.Delete command
So, for my macro, as I have already selected the row with
Cells(i, "C")
I can just attach this command to that selection, giving
Cells(i, "C").EntireRow.Delete
I use this technique all of the time - record the bits that you can and write the bits that you cannot.
Getting Help
When you are trying to determine the exact code to use you should make extensive use of the Help system that VBA offers; it really is quite impressive. Obviously you can access the help system as you would for any Microsoft product, but VBA also offers syntax specific help. For instance, if you know you want to use the Cells command, but don't know all of the possible options, then click your cursor on the word Cells and then press the "F1" key. The help dialogue will open up and display the Cells syntax.
Fig 2.01 - The help system.
Another wonderful feature of VBA is the code completion option. This is activated when you start to type the syntax of a statement and generally enter a period ".", the system will then popup a window for you to select an option from. For example, when I start to define a variable, after I type the "as" the popup window will appear and as I type "in" for Integer it will highlight that choice - to select, just hit tab.
Fig 2.02 - The code completion when defining variables
In this example we see the options available to the Worksheets object, displayed in a dropdown box when I type the "."
Fig 2.03 - Some of the methods available to the Worksheets object.
This option is very useful when you are trying to find the specific functionality to use.
Print this page
Latest Articles