Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Get text from last and first cell in a column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get text from last and first cell in a column
kima wrote:
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. -- I will not bow down, I'll rise through the sun. I'm transcending. I am claiming my throne. We are immortal; we rise from the wraith; we are eternal. You are my blood legion. My faith. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get text from last and first cell in a column
On Wed, 7 Nov 2012 09:04:00 +0000, kima wrote:
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 |
#4
|
|||
|
|||
Thx Auric and Ron
Now it works perfect. U have been very helpfull. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to put the text in the cell of column B in front of the the text in column D. | Excel Programming | |||
Compare text string of a cell in Column A VS another cell in Colum | Excel Discussion (Misc queries) | |||
Compare text string of a cell in Column A VS another cell in Colum | Excel Worksheet Functions | |||
Compare text string of a cell in Column A VS another cell in Colum | Excel Programming | |||
Deleting Rows based on text in cell & formatting cell based on text in column beside it | Excel Programming |