Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default Counting Colums with Data

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

Thanks
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 14
Default Counting Colums with Data


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


-----Original Message-----
Can someone help me with some code to count the number of

columns that
have data in them?

Thanks
.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default Counting Colums with Data

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



"Smythe32" wrote in message
om...
Can someone help me with some code to count the number of columns that
have data in them?

Thanks



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Counting Colums with Data

Thank you. That was enough to get me where I needed to go.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting data from 2 differant colums Kelly******** Excel Worksheet Functions 1 December 24th 09 04:43 AM
How to separate x y data in 1 column to 2 colums of x and y data? DaHou Excel Discussion (Misc queries) 6 July 17th 09 05:33 PM
Counting colums with '0' in Excel KK Excel Discussion (Misc queries) 1 February 16th 07 10:13 AM
Counting different colums gadget New Users to Excel 1 November 8th 05 03:06 PM
same data colums thanks Excel Worksheet Functions 1 May 3rd 05 03:37 PM


All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"