Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find last non empty cell in a column and display value in msgbox
Hi,
Here's a couple of methods, but the first requires you to have the active cell in the range that you are using, and also it will require you not to have a contiguous range (the CurrentRegion identifies all contiguous cells that are not blank into one rectangular block, so any rows or columns within that block that are completely empty will end the range). The second identifies the last cell in the chosen column, and uses a predefined first row (both methods have a predefined column). Public Sub MsgAndSum() Const intColumnNumber As Integer = 2 Const lngFirstRow As Long = 1 'METHOD 1 MsgBox (Cells(ActiveCell.CurrentRegion.Rows.Count + ActiveCell.CurrentRegion.Row - 1, _ intColumnNumber).Value) Cells(ActiveCell.CurrentRegion.Rows.Count + ActiveCell.CurrentRegion.Row, intColumnNumber).Value _ = Application.WorksheetFunction.Sum(Range(Cells(Acti veCell.CurrentRegion.Row, _ intColumnNumber), Cells(ActiveCell.CurrentRegion.Rows.Count + _ ActiveCell.CurrentRegion.Row - 1, intColumnNumber))) 'METHOD 2 MsgBox (Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp)) Cells(Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp).Row + 1, intColumnNumber).Value = _ Application.WorksheetFunction.Sum(Range(Cells(lngF irstRow, intColumnNumber), _ Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp))) End Sub Both methods have drawbacks, 1 requires the activecell to be in the range and all the data to be contiguous. But if you have more data below the range then it is better as Method 2 will look at all the data in the column even if non-contiguous. There will be many more methods, the best will depend on knowing how your data is formed. Cheers, Sean. -- (please remember to click yes if replies you receive are helpful to you) "vbnewbie" wrote: I have a column of numbers and wish to display the last non empty cell value in the column in a msgbox. In rows where there are no values entered the cells in this column have formulae until updated manually. e.g. Row Column A Column B 1 28/01/19 2106 2 30/01/09 2107 < value I wish to display in msgbox 3 =SUM(B2+1 ) I wish to add the value using a macro Thanks in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find last non empty cell in a column and display value in msgb
Hi
Using your method two I got syntax error! "SeanC UK" wrote: Hi, Here's a couple of methods, but the first requires you to have the active cell in the range that you are using, and also it will require you not to have a contiguous range (the CurrentRegion identifies all contiguous cells that are not blank into one rectangular block, so any rows or columns within that block that are completely empty will end the range). The second identifies the last cell in the chosen column, and uses a predefined first row (both methods have a predefined column). Public Sub MsgAndSum() Const intColumnNumber As Integer = 2 Const lngFirstRow As Long = 1 'METHOD 1 MsgBox (Cells(ActiveCell.CurrentRegion.Rows.Count + ActiveCell.CurrentRegion.Row - 1, _ intColumnNumber).Value) Cells(ActiveCell.CurrentRegion.Rows.Count + ActiveCell.CurrentRegion.Row, intColumnNumber).Value _ = Application.WorksheetFunction.Sum(Range(Cells(Acti veCell.CurrentRegion.Row, _ intColumnNumber), Cells(ActiveCell.CurrentRegion.Rows.Count + _ ActiveCell.CurrentRegion.Row - 1, intColumnNumber))) 'METHOD 2 MsgBox (Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp)) Cells(Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp).Row + 1, intColumnNumber).Value = _ Application.WorksheetFunction.Sum(Range(Cells(lngF irstRow, intColumnNumber), _ Cells(ActiveSheet.Rows.Count, intColumnNumber).End(xlUp))) End Sub Both methods have drawbacks, 1 requires the activecell to be in the range and all the data to be contiguous. But if you have more data below the range then it is better as Method 2 will look at all the data in the column even if non-contiguous. There will be many more methods, the best will depend on knowing how your data is formed. Cheers, Sean. -- (please remember to click yes if replies you receive are helpful to you) "vbnewbie" wrote: I have a column of numbers and wish to display the last non empty cell value in the column in a msgbox. In rows where there are no values entered the cells in this column have formulae until updated manually. e.g. Row Column A Column B 1 28/01/19 2106 2 30/01/09 2107 < value I wish to display in msgbox 3 =SUM(B2+1 ) I wish to add the value using a macro Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find first empty cell in column | Excel Discussion (Misc queries) | |||
find empty cells in a column then append row that empty cell is in | Excel Programming | |||
Macro display Msgbox when cells are empty | Excel Discussion (Misc queries) | |||
Find a empty cell in next column | Excel Discussion (Misc queries) | |||
How to find next empty cell within a column? | Excel Programming |