PDA

View Full Version : Solved: counting columns



tkaplan
07-12-2006, 01:19 PM
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
07-12-2006, 02:07 PM
First select the range then run


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

tkaplan
07-12-2006, 02:11 PM
thank you.

mdmackillop
07-12-2006, 02:15 PM
or, without selecting

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

tkaplan
07-12-2006, 02:19 PM
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
07-12-2006, 02:22 PM
CountA will count all non-blank cells in the selected rows, deduct the number of rows to discount the headings.

tkaplan
07-12-2006, 02:24 PM
so if i select A1 through E1 and they all have data it will return 5?

mdmackillop
07-12-2006, 02:36 PM
Here's an example. The formulae on the sheet are just for information.

tkaplan
07-12-2006, 02:39 PM
got it.
thank you.