Results 1 to 9 of 9

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

  1. #1
    Junior Member
    Join Date
    Sep 2017
    Posts
    5
    Rep Power
    0

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

    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.
    Attached Files Attached Files

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    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...
    Code:
    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

  3. #3
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    I prefer import.

  4. #4
    Junior Member
    Join Date
    Sep 2017
    Posts
    5
    Rep Power
    0
    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!

  5. #5
    Junior Member
    Join Date
    Sep 2017
    Posts
    5
    Rep Power
    0
    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.

  6. #6
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    You wrote:

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

  7. #7
    Junior Member
    Join Date
    Sep 2017
    Posts
    5
    Rep Power
    0
    Ha-haha, fell asleep at the wheel. Thx for the laugh snb!

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by J.Suarez View Post
    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

  9. #9
    Junior Member
    Join Date
    Sep 2017
    Posts
    5
    Rep Power
    0
    Awesome, thanks Sir! I appreciate your fine efforts!

Similar Threads

  1. VBA Code To Run Through A Column And Email Blanks
    By hooper12345 in forum Excel Help
    Replies: 1
    Last Post: 02-08-2016, 10:25 AM
  2. Replies: 3
    Last Post: 02-20-2015, 07:03 PM
  3. Replies: 0
    Last Post: 12-24-2013, 01:36 PM
  4. Replies: 2
    Last Post: 09-24-2012, 11:19 PM
  5. Formula To Identify A Cell Has Duplicate Words
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 07-03-2011, 05:52 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •