I've got a list of addresses in Excel spread over 5 columns (Address
Line 1, Address Line 2 and so on). Obviously not all addresses contain
data in all 5 columns, some may have it in A,B and E others in A,D and E
etc

What I need to do is shift all the data for these addresses across to
the left with any blank columns left at the end. So for example an
address comprising 3 elements will take up columns A,B+C with D+E
blank.

I'm after some VB code I can use to automate this task for me ( I need
to run this every week for hundreds of addresses). I've tried to record
a macro using nested if statements to test cells for being blank etc but
cant get it working due to what I can only assume is complete
incompetence on my part:

If anyone can help out would be much appreciated!


--
mr starface
------------------------------------------------------------------------
mr starface's Profile: http://www.excelforum.com/member.php...o&userid=30520
View this thread: http://www.excelforum.com/showthread...hreadid=502111

If the cells are truly blank, you should only need:

Sub FixDate()
Columns(1).Resize(,5).SpecialCells( _
xlBlanks).Delete shift:=xlShiftToLeft
End Sub

--
Regards,
Tom Ogilvy


"mr starface"
wrote in message
...

I've got a list of addresses in Excel spread over 5 columns (Address
Line 1, Address Line 2 and so on). Obviously not all addresses contain
data in all 5 columns, some may have it in A,B and E others in A,D and E
etc

What I need to do is shift all the data for these addresses across to
the left with any blank columns left at the end. So for example an
address comprising 3 elements will take up columns A,B+C with D+E
blank.

I'm after some VB code I can use to automate this task for me ( I need
to run this every week for hundreds of addresses). I've tried to record
a macro using nested if statements to test cells for being blank etc but
cant get it working due to what I can only assume is complete
incompetence on my part:

If anyone can help out would be much appreciated!


--
mr starface
------------------------------------------------------------------------
mr starface's Profile:
http://www.excelforum.com/member.php...o&userid=30520
View this thread: http://www.excelforum.com/showthread...hreadid=502111