Select Column and Identify First and Last non blank cell value per segment (Range between Blanks)

[Rick Rothstein]

Here are macros for each of the results you asked for. The first (SegmentsDown) gives the first results you asked for and the second (SegmentsAcross) gives the second result you asked for...

Sub SegmentsDown()
  Dim RowNum As Long, Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    RowNum = RowNum + 1
    Cells(RowNum, "B").NumberFormat = "@"
    Cells(RowNum, "B").Value = Ar(1) & "-" & Ar(Ar.Count)
  Next
End Sub

Sub SegmentsAcross()
  Dim Col As Long, Ar As Range
  Col = 1
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Col = Col + 1
    Ar.Copy Cells(1, Col)
  Next
End Sub