Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding First Empty Cell in a Column | Excel Discussion (Misc queries) | |||
Finding First Empty Cell in a Column | Excel Discussion (Misc queries) | |||
Finding a cell and returning value from a different row and column | Excel Discussion (Misc queries) | |||
Finding last cell with data in a column | Setting up and Configuration of Excel | |||
Finding next Blank Cell in Column | Excel Programming |