Remove blank cells from an array

[vivek456]

Hello - I have an array with different names (Array 1). I want to remove the blanks in this column & create an output in another column, where the blanks are removed. I have attempted to do this however I am struggling to achieve this. Any help on this would be much appreciated. Any advise in doing this via a macro approach or a formula based approach would be much appreciated.

Array 1	Output
Name 1	Name 1
Name 2	Name 2
	Name 4
Name 4	Name 5
Name 5	Name 6
Name 6	Name 8
Name 8

<tbody>
</tbody>

 

[Peter_SSs]

For a macro approach, try this in a copy of your workbook.

Sub NewList()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -1).Value
    On Error Resume Next
    .SpecialCells(xlBlanks).Delete Shift:=xlUp
    On Error GoTo 0
  End With
End Sub

 

[Rick Rothstein]

Hello - I have an array with different names (Array 1). I want to remove the blanks in this column & create an output in another column, where the blanks are removed. I have attempted to do this however I am struggling to achieve this. Any help on this would be much appreciated. Any advise in doing this via a macro approach or a formula based approach would be much appreciated.

Array 1	Output
Name 1	Name 1
Name 2	Name 2
	Name 4
Name 4	Name 5
Name 5	Name 6
Name 6	Name 8
Name 8

<tbody>
</tbody>

Your layout is not entirely clear, but let's assume your names list with the blanks is in Column A and starts on Row 2 and that the output list should start on cell B2. I am also assuming your names list are constants (that is, not formula results). Given those assumptions, this single line of code will produce the list you want...

[table="width: 500"]
[tr]
	[td]Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants).Copy Range("B2")[/td]
[/tr]
[/table]