Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count columns in discontinuous range (VBA/Excel 2002 SP3)

    Hi,
    I use the following code to count the number of columns:
    Set SelectedRange = Selection
    TotalCols = SelectedRange.Columns.Count

    This works fine unless I have a discontinuous selection (i.e. from Ctr click). Then it will only count the number of columns in the first continuous part of the selection.

    How do I count columns in a discontinuous range?

    Thanks!
    -Alex

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count columns in discontinuous range (VBA/Excel 2002 SP3)

    You can use the Areas property of the selection:

    Dim rng As Range
    Dim TotalCols As Integer
    For Each rng In Selection.Areas
    TotalCols = TotalCols + rng.Columns.Count
    Next rng
    MsgBox TotalCols

    Note that results may not be what you expect or want if you use discontiguous areas whose columns overlap, e.g. A1:F4 and D7:H9.

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count columns in discontinuous range (VBA/Exc

    Hi Hans,

    Brilliant. That should work (my columns don't overlap).

    Thanks a million, as always.
    -Alex

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count columns in discontinuous range (VBA/Exc

    Hi Alex
    Further to Hans' code, the following will give a count of the columns even if they do overlap. <pre>Sub countcols()
    Dim i As Long
    Dim j As Long
    Dim rng As Range
    Dim TotalCols As Integer
    Dim Ranges() As Long

    ReDim Ranges(Selection.Areas.Count - 1, 1)
    ' 0 = first column
    ' 1 = last column
    i = 0
    For Each rng In Selection.Areas
    TotalCols = TotalCols + rng.Columns.Count
    Ranges(i, 0) = rng.Column
    Ranges(i, 1) = rng.Column + rng.Columns.Count - 1
    i = i + 1
    Next rng

    For i = 0 To Selection.Areas.Count - 2
    If Ranges(i, 1) > 0 Then
    For j = i + 1 To Selection.Areas.Count - 1
    If Ranges(i, 1) > 0 And Ranges(j, 1) > 0 Then
    If Ranges(j, 0) >= Ranges(i, 0) And _
    Ranges(j, 1) <= Ranges(i, 1) Then
    TotalCols = TotalCols + Ranges(j, 0) - Ranges(j, 1) - 1
    Ranges(j, 1) = 0
    ElseIf Ranges(i, 0) >= Ranges(j, 0) And _
    Ranges(i, 1) <= Ranges(j, 1) Then
    TotalCols = TotalCols + Ranges(i, 0) - Ranges(i, 1) - 1
    Ranges(i, 1) = 0
    ElseIf Ranges(i, 0) < Ranges(j, 0) And _
    Ranges(i, 1) > Ranges(j, 0) And _
    Ranges(i, 0) < Ranges(j, 1) Then
    TotalCols = TotalCols + Ranges(j, 0) - Ranges(i, 1) - 1
    Ranges(j, 0) = Ranges(i, 1) + 1
    ElseIf Ranges(j, 0) < Ranges(i, 0) And _
    Ranges(j, 1) > Ranges(i, 0) And _
    Ranges(j, 0) < Ranges(i, 1) Then
    TotalCols = TotalCols + Ranges(i, 0) - Ranges(j, 1) - 1
    Ranges(j, 1) = Ranges(i, 0) - 1
    End If
    End If
    Next j
    End If
    Next i

    MsgBox TotalCols

    End Sub
    </pre>

    Regards
    Don

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count columns in discontinuous range (VBA/Exc

    I guess "number of columns" depends on what you want to count...

    One could use the following function. It counts each column only once, even if it occurs in more than one area in the specified range.

    Function CountCols(rng As Range) As Long
    Dim area As Range
    Dim col As Range
    Dim cols As New Collection
    On Error Resume Next
    For Each area In rng.Areas
    For Each col In area.Columns
    cols.Add col.Column, CStr(col.Column)
    Next col
    Next area
    CountCols = cols.Count
    End Function

    Use like this:

    Debug.Print CountCols(Selection)

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count columns in discontinuous range (VBA/Exc

    Thank you Hans
    That is a worthwhile lesson in efficiency.
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •