Message box that shows the column and the last data in that column.

[danbates]

Hi,

I've tried looking this up but I haven't found anything close to what I am wanting.

I would like a code that when triggered, shows the column letter and what data is in the last row of that column in a message box.

The columns are A to L.

I hope this makes sense and any help would be much appreciated.

Thanks

Dan

 

[Peter_SSs]

I was wondering if it was possible to do the following:

1. Double tab columns A, B, C & F and single tab the rest? It would line everything up perfectly.

Try ..

Sub LastValuesAllColumns_v3()
  Dim i As Long
  Dim msg As String
  Dim cLast As Range
  Dim aTabs As Variant
  
  aTabs = Split("2 2 2 1 1 2 1 1 1 1 1 1")
  For i = 1 To 12
    Set cLast = Columns(i).Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious)
    msg = msg & vbLf & Cells(2, i).Value & " :"
    If cLast.Row > 2 Then msg = msg & String(aTabs(i - 1), vbTab) & cLast.Text
  Next i
  MsgBox msg
End Sub

2. Have column C & F in red?

No, but you could highlight them by adding the blue lines of code below.
You might need to experiment with the red numbers to suit your headings & data.

Sub LastValuesAllColumns_v4()
  Dim i As Long
  Dim msg As String
  Dim cLast As Range
  Dim aTabs As Variant
  
  aTabs = Split("2 2 2 1 1 2 1 1 1 1 1 1")
  For i = 1 To 12
    Set cLast = Columns(i).Find(What:="*", LookIn:=xlValues, SearchDirection:=xlPrevious)
    If i = 3 Or i = 6 Then msg = msg & vbLf & String(40, "-")
    msg = msg & vbLf & Cells(2, i).Value & " :"
    If cLast.Row > 2 Then msg = msg & String(aTabs(i - 1), vbTab) & cLast.Text
    If i = 3 Or i = 6 Then msg = msg & vbLf & String(40, "-")
  Next i
  MsgBox msg
End Sub

 

[danbates]

Hi Peter_SSs,

I just wanted to thank you for all your help, my message box is perfect now.

Regards

Dan

 

[Peter_SSs]

Hi Peter_SSs,

I just wanted to thank you for all your help, my message box is perfect now.

Regards

Dan

Cheers. Glad we got something suitable in the end. Thanks for the follow-up.