Shift cells left if adjacent columns are blank using VBA

[Trebor8484]

Hi all,

Can anyone assist with a vba solution to the below problem please.

So I have from column I to column R columns labelled as follows:

Job Desc 1
Quantity 1
Job Desc 2
Quantity 2
Job Desc 3
Quantity 3
Job Desc 4
Quantity 4
Job Desc 5
Quantity 5

What I need to do is, working from right to left identify if there are any charges listed in Job Desc 5/Quantity 5. If there are but Job Desc 4/Quantity 4 are empty then it needs to move the values from Job Desc 5/Quantity 5 into Job Desc 4/Quantity 4. Basically, there can be no empty columns. If there are charges then they need to be populated in Job Desc 1/Quantity 1 first before moving across.

[/IMG]

Thanks

 

[Rick Rothstein]

See if this does what you want...

[table="width: 500"]
[tr]
	[td]Sub ShiftLeftColumnItoR()
  Dim LastRow As Long
  LastRow = Columns("I:R").Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  Range("I2:R" & LastRow).SpecialCells(xlBlanks).Delete xlShiftToLeft
End Sub[/td]
[/tr]
[/table]

 

[Trebor8484]

Thanks Rick, that works great

I was thinking I would end up having to write a for loop with range offsets and lots of copy and pasting. Didn't realise a one liner would do it....!

 

[Trebor8484]

Rick, I have found an issue with this where if there are values in adjacent columns, for example column S then it shifts them as well. I need it to ignore anything that isn't in the specified range I:R