find last non empty cell in a column and display value in msgbox

  • Thread starter Thread starter vbnewbie
  • Start date Start date
V

vbnewbie

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
 
Sub lvalue()
Dim n As Long, v As Variant
n = Cells(Rows.Count, 2).End(xlUp).Row
v = Cells(n, 2).Value
MsgBox (v)
End Sub
 
and if you don't want to see the formula result, try:

Sub lvalue()
Dim n As Long, i As Long
n = Cells(Rows.Count, 2).End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, 2).HasFormula Then
Else
MsgBox (Cells(i, 2).Value)
Exit Sub
End If
Next
End Sub
 
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(ActiveCell.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(lngFirstRow,
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.
 
Gary's Student

Drat this autoformatting! The example should be column A having dates,
column B having numbers.
What I need to do is show the value of column B in the the last row in which
column A is not empty in my message box. All I get with your method is an
empty message box?
 
Hi
Using your method two I got syntax error!

SeanC UK said:
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(ActiveCell.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(lngFirstRow,
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.
Click to expand...
 
See previous post!

Gary''s Student said:
and if you don't want to see the formula result, try:

Sub lvalue()
Dim n As Long, i As Long
n = Cells(Rows.Count, 2).End(xlUp).Row
For i = n To 1 Step -1
If Cells(i, 2).HasFormula Then
Else
MsgBox (Cells(i, 2).Value)
Exit Sub
End If
Next
End Sub
Click to expand...
 
Gary's Student

Thanks for all your help, I had to 'frig' it "a bit" but your code was very
helpful
 
Back
Top