PDA

View Full Version : Move data over using vba



daniels012
03-09-2011, 10:12 AM
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
03-09-2011, 10:28 AM
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

daniels012
03-09-2011, 11:07 AM
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
03-09-2011, 11:48 AM
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
03-09-2011, 11:53 AM
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
03-09-2011, 03:35 PM
Change 17 to 18 in the For statement.

daniels012
03-09-2011, 04:43 PM
That is awesome!
Can I ask one more, I think VERY difficult thing??:eek:

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
03-10-2011, 02:01 AM
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