ExcelBanter - Arrange column after delete

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Arrange column after delete (https://www.excelbanter.com/excel-programming/360758-arrange-column-after-delete.html)

Arrange column after delete

Hello,

I have a worksheet with data:

SerialNumber Name
1 Dan
2 Joe
3 Kelvin
4
5
6
7
8
9
10

My user sometimes delete one name, for example:
1
2 Joe
3 Kelvin
4
5
6
7
8
9
10

I want to write a macro that will shift the remaining names up:

1 Joe
2 Kelvin
3
4
5
6
7
8
9
10

How can I do this ?

Thank you
Gil D.

Arrange column after delete

I guess, that names are in column "B".

Try this:
Private Sub CommandButton1_Click()
For Each cell In Range("B1:B10")
If cell.Value = "" Then cell.Delete Shift:=xlUp
Next cell
End Sub

Kind Regards,
Andrzej

Gil napisał(a):
Hello,

I have a worksheet with data:

SerialNumber Name
1 Dan
2 Joe
3 Kelvin
4
5
6
7
8
9
10

My user sometimes delete one name, for example:
1
2 Joe
3 Kelvin
4
5
6
7
8
9
10

I want to write a macro that will shift the remaining names up:

1 Joe
2 Kelvin
3
4
5
6
7
8
9
10

How can I do this ?

Thank you
Gil D.

Arrange column after delete

Thank you very much for your help.