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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Array 1[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]Name 1[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Name 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name 4[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]Name 5[/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD]Name 6[/TD]
[/TR]
[TR]
[TD]Name 6[/TD]
[TD]Name 8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Array 1[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]Name 1[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]Name 2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name 4[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]Name 5[/TD]
[/TR]
[TR]
[TD]Name 5[/TD]
[TD]Name 6[/TD]
[/TR]
[TR]
[TD]Name 6[/TD]
[TD]Name 8[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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]