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!

Hi Arne

Just a little typo i think

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

Laterz

S

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
(E-Mail Removed)
"Arne Hegefors" <(E-Mail Removed)> wrote in message
news:7BA27CCB-4EC7-4D32-ACB4-(E-Mail Removed)...
> 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!
>