Counting Colums with Data

  • Thread starter Thread starter Smythe32
  • Start date Start date
S

Smythe32

Can someone help me with some code to count the number of columns that
have data in them?

Thanks
 
Sub ct()
Dim s As Range
Dim c As Integer
Dim x As Integer
Set s = ActiveSheet.UsedRange
For c = 1 To s.Columns.Count
If Application.CountA(s.Columns(c)) > 0 Then
x = x + 1
End If
Next c
MsgBox ("There are " & x & " Columns with data")
End Sub
 
Sub countcolumns()
Dim rng As Range, rng1 As Range, rng2 As Range
On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlFormulas)
Set rng1 = ActiveSheet.Cells.SpecialCells(xlConstants)
On Error GoTo 0
If rng Is Nothing And Not rng1 Is Nothing Then
Set rng2 = rng1
ElseIf Not rng Is Nothing And rng1 Is Nothing Then
Set rng2 = rng
Else
Set rng2 = Union(rng, rng1)
End If
msgbox Intersect(rng2.EntireColumn, Rows(1)).Count


End Sub


This may be more than you need, but without knowing more about your
worksheet, it should work in all cases but the case of a blank sheet.

Regards,
Tom Ogilvy
 
Back
Top