C#, VB.Net, XML and mass consumption of coffee.

I know not too many people are coding VBA macros, however if you think about it automating office tasks can be a good way to help business people.  I can tell you that most coders will not touch it thinking that users should be able to do this themselves, most users will not be able to do this, simply because you need to actually write the code.  I can tell you that knowing VBA earned me a pretty coin, and trust me, this is not too hard to learn. 

Here is an example of how to remove all blank rows from a table in excel:

  | 
 copy code |
?
 
01
Public Sub main()
02
 
03
    Dim rBody As Range
04
    Dim iRow As Integer
05
    Dim iCell As Integer
06
    Dim iStoreToKill() As Integer
07
    Dim iIndexer As Integer
08
    Dim iEmptyCounter As Integer
09
 
10
    iEmptyCounter = 0
11
    iIndexer = 0
12
 
13
    Set rBody = Range("BODY").Cells
14
 
15
    For iRow = 1 To rBody.Rows.Count
16
 
17
        If rBody.Cells(iRow, 1).Value = vbNullString Then
18
            iEmptyCounter = 1
19
            For iCell = 2 To rBody.Columns.Count
20
                If rBody.Cells(iRow, iCell).Value = vbNullString Then
21
                    iEmptyCounter = iEmptyCounter + 1
22
                End If
23
            Next
24
 
25
            If iEmptyCounter = rBody.Columns.Count Then
26
                iIndexer = iIndexer + 1
27
                ReDim Preserve iStoreToKill(1 To iIndexer)
28
                iStoreToKill(iIndexer) = iRow
29
            End If
30
        End If
31
    Next
32
 
33
    If iIndexer > 0 Then
34
        For i = iIndexer To 1 Step -1
35
            rBody.Rows(iStoreToKill(i)).EntireRow.Delete
36
        Next
37
    End If
38
 
39
End Sub

Let’s take a closer look at this:

Lines 3 through 8 – we need couple of variables to get this done.  On line 3 we are declaring an object of type Range which is a collection of cells, line 6 introduces an array where we are going to store indexes of rows which are blank and needs to be deleted.

On line 8 we are actually assign our range.  In this case, the range was pre-defined, but you can easily declare your own ranges by specifying cell coordinates.  Now let’s get this thing going – line 15 we are going through all the rows within our range to see if we have any blank rows.  Line 17 checks if the first cell in the row is empty.  If it is, we are looking at all the cells within the row and increment our emptyCounter every time we find a blank cell (lines 19 through 23). 

Lines 25 through 29 – we want to see if our counter of empty cells in this particular row matches with the number of columns in the range.  If we have a match, this means that basically all the cells in the row are blank.  Line 27 – we need to resize the array, while preserving all the values that are already there.  VBA does not have ArrayLists or any dynamically sized collections, so the only way to add an element to an array when you have no idea what the total capacity of the array would be before you start is to do redim preserve.    This way my iStoreToKill will contain the indexes of all blank rows in the range.

Lines 33 through 37 – well, if our iStoreToKill is not empty, we want to remove some of the rows.  Line 34 – we are going through our array backwards.  Why backwards – well, so your indexes will not change on you when you delete the rows going down.  Lets imagine we have this thing going:

1 First row



2



3 Third row



4



5 Fifth row



We need to remove 2nd and 4th rows, right?  So if we go through array forward, we kill row number 2 first and then you get this type of contruct -



1 First row



2 Third row



3



4 Fifth row



On your second iteration, we will kill row number 4 which would be the one tagged “Fifth row” and this is not what we are after here.  After we are all done, we should get the table without any blank rows.



Keep in mind – you need to goof around with security settings of Excel.  If you are doing macros – get yourself code signing certificate – it will help you distribute your macros.



I would love to hear a feedback on this post – I am not sure if I should come up with more VBA examples.