Monday, August 8, 2011

Counting the number of columns

I often run into situations where I need to count the number of columns between two cells.  For example, to make sure that my data processing script extracted the correct number of stats, I need to make sure that the number of columns in the CSV file is equal to the number of stat counters.  Now counting the number of rows is easy since the rows are labeled by numbers.  For columns, however, they are labeled in letters.  At least for me, I cannot easily work out how many columns are there between, say, column B and column AAB.

I have written a simple macro to determine the number of columns between two selected cells (or selected columns). The following is the first version I wrote. It looks for the two areas selected by the user, determines their corresponding column number, and computes the number of columns between them. If only one area is selected, it will just print out the column number of the selected area. (More on selection areas below.)



   1:  Sub Count_columns_v1()
   2:      ' Computes the number of columns between two selected columns.
   3:      ' If only one column is selected, display the column number.
   4:      Dim MAX_AREAS As Integer
   5:      Dim col_1, col_2 As Integer
   6:      
   7:      If ActiveSheet Is Nothing Then
   8:          Exit Sub
   9:      End If
  10:      
  11:      MAX_AREAS = 2
  12:      
  13:      ' do not allow user to select more than 2 areas.
  14:      If Selection.Areas.count > MAX_AREAS Then
  15:          MsgBox "Please select the starting " & _
  16:              "and ending columns properly."
  17:          Exit Sub
  18:      End If
  19:      
  20:      If Selection.Areas.count = 1 Then
  21:          col_1 = Selection.Areas(1).Column
  22:          MsgBox "The column # selected is " & col_1
  23:      Else
  24:          col_1 = Selection.Areas(1).Column
  25:          col_2 = Selection.Areas(2).Column
  26:          
  27:          MsgBox "The starting column # is " & col_1 & _
  28:              ".  " & "The ending column # is " & _
  29:              col_2 & "." & Chr(13) & Chr(13) & _
  30:              "The number of columns in between " & _
  31:              "(including start and end columns) = " _
  32:              & col_2 - col_1 + 1 & "."
  33:      End If
  34:      
  35:  End Sub

To test out the macro, click on one cell (say B2), and then hold down control and click on another cell (say D4), and then run the macro.  You will see the following output.

output1

One limitation on using this script is that the user needs to control-select two cells or columns, and cannot drag-select multiple columns. This is because when a user drag-selects multiple cells or columns, Excel treats the selection as belonging to the same selection area.

When the user selects a cell or a bunch of cells, Excel makes them available as a selection object (which is a range object).  A selection object has the concept of areas.  An area is a continuous area of cells selected by the user.  The user can select multiple areas by holding down the control key and drag-select cells.  For example, the following can be selected by first clicking on cell B2, and then holding down the control key while dragging from cell D4 to E5.

selection1

In this example, the selection has 2 areas.  The first area has one cell, and the second area has 4 cells.  The number of areas in a selection object can be determined by

Selection.Areas.count

Selection.Areas is a collection in VBA.  The expression  Selection.Areas.count determines the number of objects in the collection.  The individual objects can be accessed using a subscript as in Selection.Areas(1) and Selection.Areas(2) in the macro.

This first version of macro first determines the number of areas selected by the user.  If only one area is selected, it assumes that the user only selected only one column and prints out the column number.  If two areas are selected, it will determine the column number of the two areas (Selection.Areas(1).column and Selection.Areas(2).column) and compute the difference between them.


This first version is obviously lacking in the following aspects:
  1. If the user drag-select multiple columns (either intentionally or by mistake), the macro incorrectly assumes that only one column is selected, and reports the first column of the area.
  2. Drag-selecting multiple columns is way easier than control-clicking two cells or columns. The limitation is inconveniencing the users.
  3. Similarly, if the user selected multiple columns in the first or the second area (such as the example above), the macro does not detect such selection and just uses the first column number of the respective areas to compute the difference.  Although it is odd for a user to make such a selection, it is nevertheless a use case that needs to be correctly handled.
The following is an improved version which takes care of all these issues and limitations.

   1:  Sub Count_columns()
   2:      ' Computes the number of columns between two selected columns.
   3:      ' If only one column is selected, display the column number.
   4:      Dim MAX_AREAS As Integer
   5:      Dim col_1, col_2, last_col As Integer
   6:      
   7:      If ActiveSheet Is Nothing Then
   8:          Exit Sub
   9:      End If
  10:      
  11:      MAX_AREAS = 2
  12:      
  13:      If Selection.Areas.count > MAX_AREAS Then
  14:          MsgBox "Please select the starting " & _
  15:              "and ending columns properly."
  16:          Exit Sub
  17:      End If
  18:      
  19:      If Selection.Areas.count = 1 Then
  20:          ' the user could have just selected 1 column,
  21:          ' or drag selected multiple columns.
  22:          If Selection.Areas(1).Columns.count = 1 Then
  23:              col_1 = Selection.Areas(1).Column
  24:              MsgBox "The column # selected is " & col_1
  25:          Else
  26:              last_col = Selection.Areas(1).Columns.count
  27:              col_1 = Selection.Areas(1).Columns(1).Column
  28:              col_2 = Selection.Areas(1).Columns(last_col).Column
  29:              MsgBox "The starting column # is " & col_1 & _
  30:                  ".  " & "The ending column # is " & _
  31:                  col_2 & "." & Chr(13) & Chr(13) & _
  32:                  "The number of columns in between " & _
  33:                  "(including start and end columns) = " _
  34:                  & col_2 - col_1 + 1 & "."
  35:          End If
  36:      Else
  37:          ' the user control selected two areas.
  38:          ' make sure the two areas each only contains only one column.
  39:          If Selection.Areas(1).Columns.count <> 1 _
  40:              Or Selection.Areas(2).Columns.count <> 1 Then
  41:              MsgBox "Please select the starting and " & _
  42:                  "ending columns properly." & Chr(13) & _
  43:                  "Each selected area can only contain one column."
  44:              Exit Sub
  45:          End If
  46:          
  47:          col_1 = Selection.Areas(1).Column
  48:          col_2 = Selection.Areas(2).Column
  49:          
  50:          MsgBox "The starting column # is " & col_1 & _
  51:              ".  " & "The ending column # is " & _
  52:              col_2 & "." & Chr(13) & Chr(13) & _
  53:              "The number of columns in between " & _
  54:              "(including start and end columns) = " _
  55:              & col_2 - col_1 + 1 & "."
  56:      End If
  57:  End Sub

In this version,
  1. If the user only selected one area, the macro checks to see if multiple columns are selected in that area, and handles that accordingly.  If multiple columns are selected in the same selection area, it computes the difference between the first and last column in the selection.  This allows the user to drag select multiple columns, thus removing the annoying limitations of the first version.
  2. If there are two selection areas, check to see if both of them consist of only one column.  If not, then ask the user to select again; otherwise, compute the number of columns between the two areas accordingly.
You can add this macro to your personal macro workbook and make it always available.  We have covered personal macro workbook in a previous post.




No comments:

Post a Comment