Finding the last used cell in a column
 

I have a workbook that has several worksheets in it, once
of which is suppose to display the total balances from the
other worksheets. There are a total of 4 worksheets.

Worksheets 1 to 3 each have a column that has a running
balance. (I.e. Cell_Value = Previous_Cell + Additions -
Subtractions) These sheets will have an unknown number of
used cells as it will change each time data is entered.

I'm using Excel 97 (can't afford to buy a new version) and
want to select the last used cell in the specified column.
I can't seem to find a built in function, and am having
some trouble getting Excel to find my user-defined
function. (Excel won't recognize it.)

Thus far, I've written:

Public Function Get_Last_Cell_Value( _
ByRef Current_Worksheet As Worksheet, _
ByRef Current_Column As String) As Currency
Dim Total_Count_Of_Column As Integer
Dim Column As Range
Set Column = Current_Worksheet.Columns(,
Current_Column)
Total_Count_Of_Column = Column.Count
Get_Last_Cell_Value = Column.Cells
(Total_Count_Of_Column)
End Function

which should do the trick. The function is stored
in "ThisWorkbook". It's been a while since I've done VBA.
I don't usually program Excel. Am I missing something? Is
there an easier way to do this? I'd rather avoid building
a macro if I can for security reasons.

Help much appreciated.

Ben

Finding the last used cell in a column
 

This will find the last cell in use in column B.

Enter this as an array formula (Ctrl + Shift + Enter):
=MAX((NOT(ISBLANK(B1:B65535))*ROW(B1:B65535)))

Or this one (again an array formula) will find the last numeric cell:
=MAX((ISNUMBER(B1:B65535)*ROW(B1:B65535)))

For reasons best known to Microsoft, you can't define a complete column to
make this method work, hence the formulas only go to row 65535. Maybe
they'll change that in the next release!

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Ben's Oak" wrote in message
...
I have a workbook that has several worksheets in it, once
of which is suppose to display the total balances from the
other worksheets. There are a total of 4 worksheets.

Worksheets 1 to 3 each have a column that has a running
balance. (I.e. Cell_Value = Previous_Cell + Additions -
Subtractions) These sheets will have an unknown number of
used cells as it will change each time data is entered.

I'm using Excel 97 (can't afford to buy a new version) and
want to select the last used cell in the specified column.
I can't seem to find a built in function, and am having
some trouble getting Excel to find my user-defined
function. (Excel won't recognize it.)

Thus far, I've written:

Public Function Get_Last_Cell_Value( _
ByRef Current_Worksheet As Worksheet, _
ByRef Current_Column As String) As Currency
Dim Total_Count_Of_Column As Integer
Dim Column As Range
Set Column = Current_Worksheet.Columns(,
Current_Column)
Total_Count_Of_Column = Column.Count
Get_Last_Cell_Value = Column.Cells
(Total_Count_Of_Column)
End Function

which should do the trick. The function is stored
in "ThisWorkbook". It's been a while since I've done VBA.
I don't usually program Excel. Am I missing something? Is
there an easier way to do this? I'd rather avoid building
a macro if I can for security reasons.

Help much appreciated.

Ben

Finding the last used cell in a column
 

I created an example worksheet last night to give out to
other people, and tried the following two formulas in it
based on what you gave:

=MAX((NOT(ISBLANK(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))
=MAX((NOT(ISNUMBER(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))

Neither worked. The one using ISBLANK() reported 1 and 2 -
1 when I included D1 and 2 when I didn't. The one using
ISNUMBER() returned 0. The correct value should have been
563.

Sheet 1:
A B C D
Description Withdrawl Deposit Balance
Initial $400.00 $400.00
$25.00 $375.00
$30.00 $405.00
$11.00 $394.00
$23.00 $371.00
$8.00 $363.00
$200.00 $563.00
$563.00
D1 = 400
D2 = D1 - B1 + C1
D3 = D2 - B2 + C2

Sheet2 is similiar.

And Sheet3 has the following format:
A B
Account Balance
Sheet1 $563.00
Sheet2 $683.00

Where column B is the last used cell from column D of the
respective sheets. It is not necessarily the maximum value
as the balance may go up and down based.

TIA,

Ben


-----Original Message-----
This will find the last cell in use in column B.

Enter this as an array formula (Ctrl + Shift + Enter):
=MAX((NOT(ISBLANK(B1:B65535))*ROW(B1:B65535)))

Or this one (again an array formula) will find the last
numeric cell:
=MAX((ISNUMBER(B1:B65535)*ROW(B1:B65535)))

For reasons best known to Microsoft, you can't define a
complete column to
make this method work, hence the formulas only go to row
65535. Maybe
they'll change that in the next release!

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Ben's Oak" wrote
in message
...
I have a workbook that has several worksheets in it,
once
of which is suppose to display the total balances from
the
other worksheets. There are a total of 4 worksheets.

Worksheets 1 to 3 each have a column that has a running
balance. (I.e. Cell_Value = Previous_Cell + Additions -
Subtractions) These sheets will have an unknown number
of
used cells as it will change each time data is entered.

I'm using Excel 97 (can't afford to buy a new version)
and
want to select the last used cell in the specified
column.
I can't seem to find a built in function, and am having
some trouble getting Excel to find my user-defined
function. (Excel won't recognize it.)

Thus far, I've written:

Public Function Get_Last_Cell_Value( _
ByRef Current_Worksheet As Worksheet, _
ByRef Current_Column As String) As Currency
Dim Total_Count_Of_Column As Integer
Dim Column As Range
Set Column = Current_Worksheet.Columns(,
Current_Column)
Total_Count_Of_Column = Column.Count
Get_Last_Cell_Value = Column.Cells
(Total_Count_Of_Column)
End Function

which should do the trick. The function is stored
in "ThisWorkbook". It's been a while since I've done
VBA.
I don't usually program Excel. Am I missing something?
Is
there an easier way to do this? I'd rather avoid
building
a macro if I can for security reasons.

Help much appreciated.

Ben


.

Finding the last used cell in a column
 

You must enter these as array formula - as advised. To do this enter the
formula, or if already entered press F2 to edit the contents, then hold down
Ctrl and Shift and press Enter.

Cheers
Nigel

"Ben's Oak" wrote in message
...
I created an example worksheet last night to give out to
other people, and tried the following two formulas in it
based on what you gave:

=MAX((NOT(ISBLANK(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))
=MAX((NOT(ISNUMBER(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))

Neither worked. The one using ISBLANK() reported 1 and 2 -
1 when I included D1 and 2 when I didn't. The one using
ISNUMBER() returned 0. The correct value should have been
563.

Sheet 1:
A B C D
Description Withdrawl Deposit Balance
Initial $400.00 $400.00
$25.00 $375.00
$30.00 $405.00
$11.00 $394.00
$23.00 $371.00
$8.00 $363.00
$200.00 $563.00
$563.00
D1 = 400
D2 = D1 - B1 + C1
D3 = D2 - B2 + C2

Sheet2 is similiar.

And Sheet3 has the following format:
A B
Account Balance
Sheet1 $563.00
Sheet2 $683.00

Where column B is the last used cell from column D of the
respective sheets. It is not necessarily the maximum value
as the balance may go up and down based.

TIA,

Ben


-----Original Message-----
This will find the last cell in use in column B.

Enter this as an array formula (Ctrl + Shift + Enter):
=MAX((NOT(ISBLANK(B1:B65535))*ROW(B1:B65535)))

Or this one (again an array formula) will find the last
numeric cell:
=MAX((ISNUMBER(B1:B65535)*ROW(B1:B65535)))

For reasons best known to Microsoft, you can't define a
complete column to
make this method work, hence the formulas only go to row
65535. Maybe
they'll change that in the next release!

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Ben's Oak" wrote
in message
...
I have a workbook that has several worksheets in it,
once
of which is suppose to display the total balances from
the
other worksheets. There are a total of 4 worksheets.

Worksheets 1 to 3 each have a column that has a running
balance. (I.e. Cell_Value = Previous_Cell + Additions -
Subtractions) These sheets will have an unknown number
of
used cells as it will change each time data is entered.

I'm using Excel 97 (can't afford to buy a new version)
and
want to select the last used cell in the specified
column.
I can't seem to find a built in function, and am having
some trouble getting Excel to find my user-defined
function. (Excel won't recognize it.)

Thus far, I've written:

Public Function Get_Last_Cell_Value( _
ByRef Current_Worksheet As Worksheet, _
ByRef Current_Column As String) As Currency
Dim Total_Count_Of_Column As Integer
Dim Column As Range
Set Column = Current_Worksheet.Columns(,
Current_Column)
Total_Count_Of_Column = Column.Count
Get_Last_Cell_Value = Column.Cells
(Total_Count_Of_Column)
End Function

which should do the trick. The function is stored
in "ThisWorkbook". It's been a while since I've done
VBA.
I don't usually program Excel. Am I missing something?
Is
there an easier way to do this? I'd rather avoid
building
a macro if I can for security reasons.

Help much appreciated.

Ben


.

Finding the last used cell in a column
 

For anyone wondering, I did find a solution to the problem
using VBA. I have yet to try what Nigel said for the array
formula, but here's the code from my Example.xls. The
only thing to remember about this code is that the column
desired must be the furthest left column, and it is
assumed that the row is the last row - thus the reference
cell(max row, max col) works. More work would have to be
done to get it more specific and better working.

I did try to do cell(,max col), but VBA in Excel97 didn't
like it for some reason. Any how...there's my simple
solution.

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "ThisWorkbook"
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = True
Option Explicit

Public Function Get_Cell(ByRef WrkSheet As Worksheet) As
Integer
Dim ColSet As Range
Dim Count As Integer
Dim LastCell As Range
Dim RowCount, ColCount As Integer
Dim BalanceColumn As Range
RowCount = WrkSheet.UsedRange.Rows.Count
ColCount = WrkSheet.UsedRange.Columns.Count
Set LastCell = WrkSheet.Cells(RowCount, ColCount)
Get_Cell = LastCell.Value
End Function

Private Sub Workbook_Open()
' Do this also in Before Print, On Activate, and other
events too.
Update()
End Sub

Private Sub Update()
Dim WrkSheet3 As Worksheet
Dim Cell1 As Excel.Range
Dim Cell2 As Excel.Range
Set WrkSheet3 = ThisWorkbook.Sheets(3)
Set Cell1 = WrkSheet3.Cells(2, 3)
Set Cell2 = WrkSheet3.Cells(3, 3)
Cell1.Value = Get_Cell(ThisWorkbook.Sheets(1))
Cell2.Value = Get_Cell(ThisWorkbook.Sheets(2))
End Sub


-----Original Message-----
You must enter these as array formula - as advised. To
do this enter the
formula, or if already entered press F2 to edit the
contents, then hold down
Ctrl and Shift and press Enter.

Cheers
Nigel

"Ben's Oak" wrote
in message
...
I created an example worksheet last night to give out to
other people, and tried the following two formulas in it
based on what you gave:

=MAX((NOT(ISBLANK(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))
=MAX((NOT(ISNUMBER(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))

Neither worked. The one using ISBLANK() reported 1 and
2 -
1 when I included D1 and 2 when I didn't. The one using
ISNUMBER() returned 0. The correct value should have
been
563.

Sheet 1:
A B C D
Description Withdrawl Deposit Balance
Initial $400.00 $400.00
$25.00 $375.00
$30.00 $405.00
$11.00 $394.00
$23.00 $371.00
$8.00 $363.00
$200.00 $563.00
$563.00
D1 = 400
D2 = D1 - B1 + C1
D3 = D2 - B2 + C2

Sheet2 is similiar.

And Sheet3 has the following format:
A B
Account Balance
Sheet1 $563.00
Sheet2 $683.00

Where column B is the last used cell from column D of
the
respective sheets. It is not necessarily the maximum
value
as the balance may go up and down based.

TIA,

Ben


-----Original Message-----
This will find the last cell in use in column B.

Enter this as an array formula (Ctrl + Shift + Enter):
=MAX((NOT(ISBLANK(B1:B65535))*ROW(B1:B65535)))

Or this one (again an array formula) will find the last
numeric cell:
=MAX((ISNUMBER(B1:B65535)*ROW(B1:B65535)))

For reasons best known to Microsoft, you can't define a
complete column to
make this method work, hence the formulas only go to
row
65535. Maybe
they'll change that in the next release!

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Ben's Oak" wrote
in message
...
I have a workbook that has several worksheets in it,
once
of which is suppose to display the total balances
from
the
other worksheets. There are a total of 4 worksheets.

Worksheets 1 to 3 each have a column that has a
running
balance. (I.e. Cell_Value = Previous_Cell +
Additions -
Subtractions) These sheets will have an unknown
number
of
used cells as it will change each time data is
entered.

I'm using Excel 97 (can't afford to buy a new
version)
and
want to select the last used cell in the specified
column.
I can't seem to find a built in function, and am
having
some trouble getting Excel to find my user-defined
function. (Excel won't recognize it.)

Thus far, I've written:

Public Function Get_Last_Cell_Value( _
ByRef Current_Worksheet As Worksheet, _
ByRef Current_Column As String) As Currency
Dim Total_Count_Of_Column As Integer
Dim Column As Range
Set Column = Current_Worksheet.Columns(,
Current_Column)
Total_Count_Of_Column = Column.Count
Get_Last_Cell_Value = Column.Cells
(Total_Count_Of_Column)
End Function

which should do the trick. The function is stored
in "ThisWorkbook". It's been a while since I've done
VBA.
I don't usually program Excel. Am I missing
something?
Is
there an easier way to do this? I'd rather avoid
building
a macro if I can for security reasons.

Help much appreciated.

Ben


.



.

Finding the last used cell in a column
 

Okay, that works, but what do I do with the value? It
simply returns the index of the cell, not the value of the
cell which is what I need. I did find a solution (see my
other post), but I'm still intrigued by this one and how
it could be used to get an easier solution.

Thanks!

Ben

P.S. FYI, on my other post, DO NOT put the Update() in the
Worksheet_Calculate routine as it will create an infinite
loop.

-----Original Message-----
You must enter these as array formula - as advised. To
do this enter the
formula, or if already entered press F2 to edit the
contents, then hold down
Ctrl and Shift and press Enter.

Cheers
Nigel

"Ben's Oak" wrote
in message
...
I created an example worksheet last night to give out to
other people, and tried the following two formulas in it
based on what you gave:

=MAX((NOT(ISBLANK(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))
=MAX((NOT(ISNUMBER(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))

Neither worked. The one using ISBLANK() reported 1 and
2 -
1 when I included D1 and 2 when I didn't. The one using
ISNUMBER() returned 0. The correct value should have
been
563.

Sheet 1:
A B C D
Description Withdrawl Deposit Balance
Initial $400.00 $400.00
$25.00 $375.00
$30.00 $405.00
$11.00 $394.00
$23.00 $371.00
$8.00 $363.00
$200.00 $563.00
$563.00
D1 = 400
D2 = D1 - B1 + C1
D3 = D2 - B2 + C2

Sheet2 is similiar.

And Sheet3 has the following format:
A B
Account Balance
Sheet1 $563.00
Sheet2 $683.00

Where column B is the last used cell from column D of
the
respective sheets. It is not necessarily the maximum
value
as the balance may go up and down based.

TIA,

Ben


-----Original Message-----
This will find the last cell in use in column B.

Enter this as an array formula (Ctrl + Shift + Enter):
=MAX((NOT(ISBLANK(B1:B65535))*ROW(B1:B65535)))

Or this one (again an array formula) will find the last
numeric cell:
=MAX((ISNUMBER(B1:B65535)*ROW(B1:B65535)))

For reasons best known to Microsoft, you can't define a
complete column to
make this method work, hence the formulas only go to
row
65535. Maybe
they'll change that in the next release!

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Ben's Oak" wrote
in message
...
I have a workbook that has several worksheets in it,
once
of which is suppose to display the total balances
from
the
other worksheets. There are a total of 4 worksheets.

Worksheets 1 to 3 each have a column that has a
running
balance. (I.e. Cell_Value = Previous_Cell +
Additions -
Subtractions) These sheets will have an unknown
number
of
used cells as it will change each time data is
entered.

I'm using Excel 97 (can't afford to buy a new
version)
and
want to select the last used cell in the specified
column.
I can't seem to find a built in function, and am
having
some trouble getting Excel to find my user-defined
function. (Excel won't recognize it.)

Thus far, I've written:

Public Function Get_Last_Cell_Value( _
ByRef Current_Worksheet As Worksheet, _
ByRef Current_Column As String) As Currency
Dim Total_Count_Of_Column As Integer
Dim Column As Range
Set Column = Current_Worksheet.Columns(,
Current_Column)
Total_Count_Of_Column = Column.Count
Get_Last_Cell_Value = Column.Cells
(Total_Count_Of_Column)
End Function

which should do the trick. The function is stored
in "ThisWorkbook". It's been a while since I've done
VBA.
I don't usually program Excel. Am I missing
something?
Is
there an easier way to do this? I'd rather avoid
building
a macro if I can for security reasons.

Help much appreciated.

Ben


.



.