Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Colums with Data
Can someone help me with some code to count the number of columns that
have data in them? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting data from 2 differant colums | Excel Worksheet Functions | |||
How to separate x y data in 1 column to 2 colums of x and y data? | Excel Discussion (Misc queries) | |||
Counting colums with '0' in Excel | Excel Discussion (Misc queries) | |||
Counting different colums | New Users to Excel | |||
same data colums | Excel Worksheet Functions |