Move data over using vba

[daniels012]

I need to use VBA to move some data to the left if the cell to the left is empty.
I have a range of Q2:V1725
So data might be like this:
Q2 has dafbdb
R2 "empty"
S2 has dfbsdbsdb
T2 has HDHJDJD
U2 "empty"
V2 has asdfgadb


I would like code to move any data over to the left if there is a blank. So my result of the macro would be:
Q2 = dafbdb
R2 = dfbsdbsdb
S2 = HDHJDJD
T2 = asdfgadb
U2 = "empty"
V2 = "empty"

Thank you for any assistance in this!
Michael D

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim Lastcol As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastcol = .Cells(2, .Columns.Count).End(xlToLeft).Column
For i = Lastcol - 1 To 17 Step -1

If .Cells(2, i).Value2 = "" Then

.Cells(2, i + 1).Resize(, Lastcol - 16).Copy .Cells(2, i)
End If
Next i
End With

Application.ScreenUpdating = True
End Sub
[/vba]

[daniels012]

xld,
Thank you for the quick response.
I am going to go try this in a bit.

Just curious with your code, will it matter if I have data before column Q and after column V? Because I do have data before and after those columns. That is why I specifically wanted Q through V.

Thank You,
Michael D

[Bob Phillips]

The data before Q doesn't matter, it stops at Q. V is a different matter, my code will go as far as there is data.

[daniels012]

Ok,
I added the columns I want to do to be the last columns of choice. In doing this my start column should be "R" instead of "Q". What number should I add in your code?

Thank You,
Michael D

[Bob Phillips]

Change 17 to 18 in the For statement.

[daniels012]

That is awesome!
Can I ask one more, I think VERY difficult thing??

Once I get these, is there a way to alphabetize these sideways?
Kind of like sort left/right instead of up/down on columns Q through V or excuse me R through W.

If this is too difficult and/or needs to be in a different post, please let, me know.

Michael D

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim Lastcol As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

Lastcol = .Cells(2, .Columns.Count).End(xlToLeft).Column
For i = Lastcol - 1 To 18 Step -1

If .Cells(2, i).Value2 = "" Then

.Cells(2, i + 1).Resize(, Lastcol - 17).Copy .Cells(2, i)
End If
Next i

Lastcol = .Cells(2, .Columns.Count).End(xlToLeft).Column
.Range("R2").Resize(, Lastcol - 17).Sort Key1:=.Range("R2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, Orientation:=xlLeftToRight
End With

Application.ScreenUpdating = True
End Sub
[/vba]