Solved: counting columns

[tkaplan]

it's been a while since i've touched excel vba and i forgot how to do this:
i have a spreadsheet that is based on an exported report and i am trying to format it. however, the fields exported are a differnet number each time, but always in the same format.
so it's always like this:


col A - row headings | ColB and C - plan 1 |
and each additional plan takes another 2 columns.
so i need to figure out how many plans there are.
so i go down to the row that is always filled in in every cell and i did selection.end.
so now here's my question:

say i end on col E, i need to translate that into 5 so that i can subract 1 (for the col a headers) and divide by 2 to figure out that there are 2 plans.
so how do i do this?

thanks in advance,
tkaplan

[mdmackillop]

First select the range then run

[VBA]
Sub CountPlans()
MsgBox (Application.WorksheetFunction.CountA(Selection) - Selection.Rows.Count)
End Sub

[/VBA]

[tkaplan]

thank you.

[mdmackillop]

or, without selecting
[vba]
Sub CountPlans2()
MsgBox Application.WorksheetFunction.CountA(Rows("1:" & [A1].End(xlDown).Row)) _
- [A1].End(xlDown).Row
End Sub

[/vba]

[tkaplan]

these count the rows though. i need to count the columns.
from a to e is 5 columns, from a to c is 3, etc.

[mdmackillop]

CountA will count all non-blank cells in the selected rows, deduct the number of rows to discount the headings.

[tkaplan]

so if i select A1 through E1 and they all have data it will return 5?

[mdmackillop]

Here's an example. The formulae on the sheet are just for information.

[tkaplan]

got it.
thank you.