Remove blank cells in a given range (remove gaps between cells)

[thedeadzeds]

Guys, is there a way to via vba move all of the names so there are no gaps so example 1 looks like example 2. The data is currerntly in range c22 to E40 in sheet called 'Allocate'

EXAMPLE 1

Name1	Name2	Name3
Angela Rose
Bethan Mason		Angela Rose
Chloe Whitfield	Bethan Mason
		Angela Rose
	Chloe Whitfield
		Angela Rose
Lorna Young
	Bethan Mason
Nicole Foxwell

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

EXAMPLE 2

Name1	Name2	Name3
Angela Rose	Bethan Mason	Angela Rose
Bethan Mason	Chloe Whitfield	Angela Rose
Chloe Whitfield	Bethan Mason	Angela Rose
Lorna Young
Nicole Foxwell

<colgroup><col span="2"><col></colgroup><tbody>
</tbody>

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG16Nov56
[COLOR="Navy"]Dim[/COLOR] rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] rng = Range("A:c").SpecialCells(xlCellTypeBlanks)
rng.Delete shift:=xlUp
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Fluff]

try

Sub removeblanks()
Range("C22:E40").SpecialCells(xlBlanks).Delete xlShiftUp
End Sub

 

[MickG]

You can also:- Select area.
Then "Home" > "Find & Select" > "Go to special" > "Blank"> "Ok.
Right click Area, "Select Delete" > "shift Cells Up" > "OK"