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

[J.Suarez]

I have a column which begins at A1 and continues through A65535, within this the sequence of numbers begins 1 through what ever the next highest number is, until a blank or dashes appears and then it continues on to the next available number. The blank or dash interval vary. I need a way to identify the 1st and last non-blank cell value between each segment or separate into new columns and the variable is either a blank or a dash example:

1
2
3
4
blank
blank
5
6
blank
blank
9
blank
10
11
12

the result should be either:

B1: 1-4
B2: 5-6
B3: 9
B4: 10-12

or

column B
1
2
3
4

column C
5
6

column D
9

column E
10
11
12

etc...

I would appreciate any assistance, I'm not an export, but can fumble through a formula or vba and/or a combination.

[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

[snb]

I prefer import.

[J.Suarez]

Mr. Rothstein, thank you for your assistance I appreciate it! Would you be able to provide a brief description of what each line does or break down the script. Only, if you have the time. Again, thank you this worked perfect for my needs and I hope it helps others!

[J.Suarez]

Hello snb, as for the import! I'm not aware of an import being able to concatenate or provide the same as what Mr. Rothstein has created. I'm interested in seeing how this would be accomplished through an import method. Please elaborate.

[snb]

You wrote:

I would appreciate any assistance, I'm not an export, but can f................

[J.Suarez]

Ha-haha, fell asleep at the wheel. Thx for the laugh snb!

[Rick Rothstein]

Mr. Rothstein, thank you for your assistance I appreciate it! Would you be able to provide a brief description of what each line does or break down the script. Only, if you have the time. Again, thank you this worked perfect for my needs and I hope it helps others!

See inline comments in red...

Sub SegmentsDown()
  ' I always declare my variables by name and type
  Dim RowNum As Long, Ar As Range
  ' Starts a loop that looks down Column A and that will iterate
  ' through each contiguous range of cells (called an Area) that
  ' contain a constant value.
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    ' RowNum is the row number that the currently iterated Area
    ' will be written to after it is formed into the hyphen delimited
    ' range that you want.
    RowNum = RowNum + 1
    ' The cell in Column B where the output will go is formatted as
    ' Text... this stops Excel from interpreting a number dash number
    ' as a date value (which is what it tends to do if the month number
    ' position is under 13
    Cells(RowNum, "B").NumberFormat = "@"
    ' The Area referenced by the Ar variable contains the values in the
    ' cells... this line of code pulls out the first value and the last value
    ' in the Area and puts a dash between them and then writes that
    ' value into the currently iterated RowNumber row in Column B.
    Cells(RowNum, "B").Value = Ar(1) & "-" & Ar(Ar.Count)
  Next
End Sub

Sub SegmentsAcross()
  ' I always declare my variables by name and type
  Dim Col As Long, Ar As Range
  ' We are going to iterate the columns in the loop that follows
  ' and since the first output column is Column B (the 2nd column)
  ' we seed the Col variable with the number 1 so that when we add
  ' one to it on the first iteration o f the loop, it increases to 2 to
  ' match the first output column.
  Col = 1
  ' Starts a loop that looks across Row 1 and that will iterate
  ' through each contiguous range of cells (called an Area) that
  ' contain a constant value.
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    ' Increase the column reference variable by one column
    Col = Col + 1
    ' This is just a simple range copy... the values in the current Area
    ' being iterated are already in a single column, so we simply copy
    ' to the range starting with the cell in Row 1 at the column specified
    ' in the Col variable.
    Ar.Copy Cells(1, Col)
  Next
End Sub

[J.Suarez]

Awesome, thanks Sir! I appreciate your fine efforts!