'VBA Split each cell value in specific range and add returned values to 1D array

I have a code written that makes an array from a specific range in my worksheet

Dim LastColumn As Long
LastColumn = Cells(Cells.Find("Parameters", lookat:=xlWhole).Row, Columns.Count).End(xlToLeft).Column

Dim PackageDepthMax 
PackageDepthMax = ThisWorkbook.Worksheets("Machine Specification").Range(Cells(Cells.Find("Package Depth(mm)").Row, 3), Cells(Cells.Find("Package Depth(mm)").Row, LastColumn)).Value

Which takes all the values from my range which could look like: enter image description here

What I am trying to do now is to have my "PackageDepthMax" consist of only the second integers in the value aka (100 , 110 , 120) instead of (1-100 , 1-110 ,1-120).

My attempt didn't do what I expect so I'm kinda lost

Dim LastColumn As Long
LastColumn = Cells(Cells.Find("Parameters", lookat:=xlWhole).Row, Columns.Count).End(xlToLeft).Column

Dim PackageDepthMax
PackageDepthMax = Split(ThisWorkbook.Worksheets("Machine Specification").Range(Cells(Cells.Find("Package Depth(mm)").Row, 3), Cells(Cells.Find("Package Depth(mm)").Row, LastColumn)).Value, " - ")(1)

What's the mistake I'm making and how do I achieve the goal I'm looking for?

arraysvbasplit


Solution 1:[1]

Keep in mind, excel will not allow split multiple columns (range of columns) at the same time.

what about replacing unwanted symbols with "" ... For example:

yourRange.Replace What:=" ", Replacement:="" to remove any gaps which may popout in wrong amounts

yourRange.Replace What:="1-", Replacement:="" removing leading "1-" from the beginning of the value

or simply yourRange.Replace "1 - ", ""

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions