Dynamic Names Pointing to Blank-Delimited Blocks of Data

[Swamp Thing]

Hello,
I have a column of data that is blank-delimited into five blocks. The size of each block will vary, since the data comes from a web query. A blank (empty) cell tells me where a block ends and the next one begins.

I would like to set up five dynamic names (e.g. Block_1, Block_2 and so on), each of which will reference the corresponding segment of data.


What would be an elegant way of defining the dynamic names?

Thanks
Swamp Thing

 

[Warship]

Sub SetNames()
    Dim i As Long, sNameCol As String
    sNameCol = "A"
    ActiveSheet.Columns(sNameCol).SpecialCells(xlCellTypeConstants, 23).Select
    For i = 1 To Selection.Areas.Count
        ActiveWorkbook.Names.Add Name:="Block_" & i, RefersTo:=Selection.Areas(i)
    Next i
End Sub

 

[tlowry]

Nice solution. I need to learn more about how SpecialCells work(s).

 

[Swamp Thing]

Thank you!

 

[mikerickson]

A non-VBA approach:
If your first block starts in A1 and each block has one blank cell between it and the next:

Define these names
Name: lastCell RefersTo: =INDEX(Sheet1!$A:$A,MATCH("zzzz",Sheet1!$A:$A),1)

Name: firstBlank
RefersTo: =MATCH("", (INDEX(Sheet1!$A:$A,1,1):lastCell)&"", 0)
Name: secondBlank
RefersTo: =MATCH("", (INDEX(Sheet1!$A:$A,firstBlank+1,1):lastCell)&"",0)+firstBlank
Name: thirdBlank
RefersTo: =MATCH("", (INDEX(Sheet1!$A:$A,secondBlank+1,1):lastCell)&"",0)+secondBlank
Name: fourthBlank
RefersTo: =MATCH("", (INDEX(Sheet1!$A:$A,thirdBlank+1,1):lastCell)&"",0)+thirdBlank

Name: Block1 RefersTo: =INDEX(Sheet1!$A:$A,1,1):INDEX(Sheet1!$A:$A,firstBlank-1,1)
Name: Block2 RefersTo: =INDEX(Sheet1!$A:$A,firstBlank+1,1):INDEX(Sheet1!$A:$A,secondBlank-1,1)
Name: Block3 RefersTo: =INDEX(Sheet1!$A:$A,secondBlank+1,1):INDEX(Sheet1!$A:$A,thirdBlank-1,1)
Name: Block4 RefersTo: =INDEX(Sheet1!$A:$A,thirdBlank+1,1):INDEX(Sheet1!$A:$A,fourthBlank-1,1)
Name: Block5 RefersTo: =INDEX(Sheet1!$A:$A,fourthBlank+1,1):lastCell

 

[Warship]

Nice solution. I need to learn more about how SpecialCells work(s).

They can be handy.

Thank you!

You're quite welcome

A non-VBA approach:...

Very very cool.

 

[Biz]

Sub SetNames()
    Dim i As Long, sNameCol As String
    sNameCol = "A"
    ActiveSheet.Columns(sNameCol).SpecialCells(xlCellTypeConstants, 23).Select
    For i = 1 To Selection.Areas.Count
        ActiveWorkbook.Names.Add Name:="Block_" & i, RefersTo:=Selection.Areas(i)
    Next i
End Sub

Nice solution. I am working on project which I can utilise your code. Thank you mate.