Your technology certification is waiting. Enroll in Cloud Class ®
The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Dim arrTemp As Variant
Dim strTemp As String
Dim rngData As Range
Dim lngRow As Long
Dim shtSheet As Worksheet
' Define the range of cells to break apart lines (all of column 1)
Set rngData = Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(ActiveSheet.Cells.Rows.Count, 1).End(xlUp))
' Copy cell values into array
arrTemp = rngData.Value
' "Flatten" array in to text string, placing <LF> between each cell's text
strTemp = ""
For lngRow = 1 To UBound(arrTemp, 1)
If strTemp = "" Then
strTemp = arrTemp(lngRow, 1)
strTemp = strTemp & arrTemp(lngRow, 1) & Chr(10)
' Split temp string of all data at each <LF>
' This creates a new array with each line of text in a different element
arrTemp = Split(strTemp, Chr(10))
' Copy the new array of lines back to the sheet, overwriting old data
Set rngData = Range("A1")
Set rngData = rngData.Resize(UBound(arrTemp), 1)
rngData.Value = Application.Transpose(arrTemp)
Open in new window
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Please enter a first name
Please enter a last name
Must be at least 4 characters long.
Join and Comment
From novice to tech pro — start learning today.
Premium members can enroll in this course at no extra cost.