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

**J.Suarez** · 09-11-2017, 08:09 PM

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!

**Rick Rothstein** · 09-11-2017, 10:43 PM

Originally Posted by 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!

See inline comments in red...

Code:

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