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

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
 
G

Gary''s Student

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
 
G

Gary''s Student

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
 
S

SeanC UK

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.
 
V

vbnewbie

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?
 
V

vbnewbie

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.
 
V

vbnewbie

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
 
V

vbnewbie

Gary's Student

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top