count number of non empty columns
 

Hi! I am trying to count the number of non empty columns of a named range. I
use this "method" for rows and that works fine but I have trouble getting it
to work for columns (I only get 0 number of columns).

Set rngPL = ThisWorkbook.Sheets("P&L Matrix").Range("PL")
Dim NoRPL As Long
Dim NoCPL As Long
NoRPL = rngPL(1).End(xlDown).Row - rngPL(1).Row + 1 'number of rows
NoCPL = rngPL.End(xlRight).Column - rngPL.Column + 1

It is the last row that is for columns but it does not work. Would be most
gratefuk for any help!

count number of non empty columns
 

Hi Arne

Just a little typo i think

if you change from xlRight to xlToRight you should have more luck

Laterz

S

count number of non empty columns
 

You might like these??
lr = Range("testrng").Cells.Find(what:="*", _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

lc = Range("testrng").Cells.Find(what:="*", _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

MsgBox lr
MsgBox lc


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Arne Hegefors" wrote in message
...
Hi! I am trying to count the number of non empty columns of a named range.
I
use this "method" for rows and that works fine but I have trouble getting
it
to work for columns (I only get 0 number of columns).

Set rngPL = ThisWorkbook.Sheets("P&L Matrix").Range("PL")
Dim NoRPL As Long
Dim NoCPL As Long
NoRPL = rngPL(1).End(xlDown).Row - rngPL(1).Row + 1 'number of rows
NoCPL = rngPL.End(xlRight).Column - rngPL.Column + 1

It is the last row that is for columns but it does not work. Would be most
gratefuk for any help!