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

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