please help i want to sort the name column such that each name starts after every blank cell.
I want it look something like this..pls help it's a pretty long column
-
How do you get the names in the name column? You use a formula? Can you show the formula or share a file with the data?Elio Fernandes– Elio Fernandes2021-09-09 11:28:17 +00:00Commented Sep 9, 2021 at 11:28
-
the file looks like the first picture, trying to sort it to look like the second. Just to rearrange "name" column such that each name is grouped with the "no" column after a blank in that order.greg616– greg6162021-09-09 11:52:55 +00:00Commented Sep 9, 2021 at 11:52
-
Does the number of names always equal with the number of "grouped" numbers?Christofer Weber– Christofer Weber2021-09-09 11:56:03 +00:00Commented Sep 9, 2021 at 11:56
-
@greg616, can you answer my first 2 questions on my previous comment?Elio Fernandes– Elio Fernandes2021-09-09 12:05:12 +00:00Commented Sep 9, 2021 at 12:05
-
@ElioFernandes no I didn't use a formula. It's just uncleaned data. Thanks.greg616– greg6162021-09-09 13:04:48 +00:00Commented Sep 9, 2021 at 13:04
|
Show 1 more comment
2 Answers
Option Explicit
Sub SetNamePosition()
Dim arr As Variant
Dim i As Long: i = 1 ' for Loop
Dim j As Long: j = 1 ' for Array
Dim lastRow As Long: lastRow = Cells(Rows.Count, 1).End(xlUp).Row
Dim rngColB As Range: Set rngColB = Range("B2:B" & lastRow)
Dim rngNames As Range: Set rngNames = Range("C1") ' Temporary range
' Get column B names only
rngColB.SpecialCells(xlCellTypeConstants, 2).Copy
rngNames.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Set rngNames = Range(rngNames, rngNames.End(xlDown))
' Load rngNames to array
arr = Application.Transpose(rngNames)
' Clear rng of column B and rngNames
rngColB.Clear
rngNames.Clear
' Insert names
For i = 2 To lastRow
' set name
Cells(i, 1).Offset(0, 1).Value = arr(j)
' find next cell
i = Cells(i, 1).End(xlDown).Row + 1
j = j + 1
Next i
End Sub
Comments
I's probably better to remove the empty ranges before making the array, but here's one way to distribute the names:
Loading the range ito an array, then go through the numbers and look for empty ranges. This assumes that we are working with column "A" and "B" (1 and 2), starting at the top.
Sub test()
Dim arr As Variant
Dim lastRow As Long, i As Long, j As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
arr = Application.Transpose(Range("B2:B" & lastRow))
Range("B2:B" & lastRow).Clear
j = 1
For i = 2 To lastRow
Cells(i, 2) = arr(j)
j = j + 1
If j >= UBound(arr) Then Exit For
While arr(j) = "" And j < UBound(arr)
j = j + 1
Wend
While Not Cells(i, 1).Value = ""
i = i + 1
Wend
Next i
End Sub
Any leftover names will be removed
