questions about Dim

[funkymonkUK]

Hi

I want to make the lastrow of sheet1 column A common knowledge between my macros. Reason being is i want to refer the the lastrow a lot of times

I thought maybe public sub would help this is my code

Public figs
' Count Rows in table
Dim lastRowpub As Long
lastRowpub = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row


It keeps coming up saying

Compile Error: Invalid Outside Procedure

and then highlights the (xlUp) part of the line


any ideas?

[Bob Phillips]

You need a sub

Public figs
Public lastRowpub As Long

Sub CountRows()
' Count Rows in table
lastRowpub = Worksheets("Sheet1").Cells(Rows.Count,"A").End(xlUp).Row
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"funkymonkUK" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi
>
> I want to make the lastrow of sheet1 column A common knowledge between
> my macros. Reason being is i want to refer the the lastrow a lot of
> times
>
> I thought maybe public sub would help this is my code
>
> Public figs
> ' Count Rows in table
> Dim lastRowpub As Long
> lastRowpub = Worksheets("Sheet1").Cells(Rows.Count,
> "A").End(xlUp).Row
>
>
> It keeps coming up saying
>
> Compile Error: Invalid Outside Procedure
>
> and then highlights the (xlUp) part of the line
>
>
> any ideas?
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=516165
>

[funkymonkUK]

thanks

so would I need to run this sub each time before using the lastrow ?

[Bob Phillips]

Yep, in fact it would be better as a function and call the function.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"funkymonkUK" <[email protected]>
wrote in message
news:[email protected]...
>
> thanks
>
> so would I need to run this sub each time before using the lastrow ?
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=516165
>

[funkymonkUK]

how i do that?

[Ardus Petus]

Function lastRowpub() As Long
' Count Rows in table
lastRowpub = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
End Function

You can use this function as follows:

set rng = cells(lasrRowpub(),"a")

HTH
--
AP

"funkymonkUK" <[email protected]> a
écrit dans le message de
news:[email protected]...
>
> how i do that?
>
>
> --
> funkymonkUK
> ------------------------------------------------------------------------
> funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
> View this thread: http://www.excelforum.com/showthread...hreadid=516165
>

[Bob Phillips]

I would actually pass the sheet and column, to make it more generic

Function lastRowpub(colnum As Long, Optional sh As Worksheet) As Long
' Count Rows in table
If sh Is Nothing Then Set sh = Activesheet
lastRowpub = sh.Cells(sh.Rows.Count, colnum).End(xlUp).Row
End Function

Then use as

iLastRow = lastRowpub(1,Worksheets("Sheet1"))


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" <[email protected]> wrote in message
news:%[email protected]...
> Function lastRowpub() As Long
> ' Count Rows in table
> lastRowpub = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
> End Function
>
> You can use this function as follows:
>
> set rng = cells(lasrRowpub(),"a")
>
> HTH
> --
> AP
>
> "funkymonkUK" <[email protected]> a
> écrit dans le message de
> news:[email protected]...
> >
> > how i do that?
> >
> >
> > --
> > funkymonkUK
> > ------------------------------------------------------------------------
> > funkymonkUK's Profile:
> http://www.excelforum.com/member.php...o&userid=18135
> > View this thread:
http://www.excelforum.com/showthread...hreadid=516165
> >
>
>