Get text from last and first cell in a column

[kima]

I have a timespan in column A:

05:54:04 2012/11/06
06:00:07 2012/11/06
...
06:23:14 2012/11/06

There are about 3000 mesurements.
Would like to make a header text with first and last cell texts:

05:54:04 2012/11/06 - 06:23:14 2012/11/06

Using a macro:

.Chart.HasTitle = True
.Chart.ChartTitle.Text = first text in column A "-" last text in colum
A


Thank in Advance!

Ki

 

[Auric__]

I have a timespan in column A:

05:54:04 2012/11/06
06:00:07 2012/11/06
..
06:23:14 2012/11/06

There are about 3000 mesurements.
Would like to make a header text with first and last cell texts:

05:54:04 2012/11/06 - 06:23:14 2012/11/06

Using a macro:

Chart.HasTitle = True
Chart.ChartTitle.Text = first text in column A "-" last text in column
A

You can try something like this:

x = Cells(1, 1).Value
If x = "" Then x = Cells(1, 1).End(xlDown).Value
y = Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1).Value
If y = "" Then _
y = Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1).End(xlUp).Value
Chart.ChartTitle.Text = x & " - " & y

If there's a header in row 1, change the first 2 lines as appropriate.

 

[Ron Rosenfeld]

I have a timespan in column A:

05:54:04 2012/11/06
06:00:07 2012/11/06
..
06:23:14 2012/11/06

There are about 3000 mesurements.
Would like to make a header text with first and last cell texts:

05:54:04 2012/11/06 - 06:23:14 2012/11/06

Using a macro:

Chart.HasTitle = True
Chart.ChartTitle.Text = first text in column A "-" last text in column
A


Thank in Advance!

Kim

Complicated but sort of self-documenting:

=============================
Option Explicit
Sub ChartTitle()
Dim sChartTitle As String
sChartTitle = CreateChartTitle(Range("A:A"))
End Sub
Function CreateChartTitle(rg As Range)
Dim FirstRow As Long, LastRow As Long
Dim c As Range
Dim i As Long

If rg.Columns.Count <> 1 Then
CreateChartTitle = "error"
Exit Function
End If

For i = 0 To rg.Count
Do
i = i + 1
Loop Until rg(i, 1).Text <> ""
FirstRow = i
Exit For
Next i
LastRow = Cells(Rows.Count, rg.Column).End(xlUp).Row
CreateChartTitle = rg(FirstRow).Text & " - " & rg(LastRow).Text
End Function
===========================


One-Liner:

Chart.ChartTitle.Text = IIf([a1].Text <> "", [a1].Text, [a1].End(xlDown).Text) & _
" - " & Cells(Rows.Count, "A").End(xlUp).Text

 

[kima]

Thx Auric and Ron

Now it works perfect. U have been very helpfull