Looping through rows and shifting certain contents
 

I'm pretty new to VBA, but love the few things I have been able to do
so far . . . here's what I want to do now:

I have a semi-colon delimited download from a program. I put it
through the text-import wizzard and it imports to columns almost
perfectly . . . Almost. Out of the 10,000 or so lines on the import, a
handful were not delimited properly on the data dump. This is
resulting in some of the data being shifted left either one or two
columns. (see example below of the last three columns of my data dump)

My pre-VBA solution was to filter the column AK by BLANK and then
manually shift the data to the right one or two rows (whatever it
needed). Now that I'm falling in love with VBA, i understand the time
that automating this process could save.

Here's what I'm trying to do in plain English

For each row I want to

If the contents of AJ are = 0 than do nothing,
If the contents of AJ are blank "", then I want to shift the contents
of Column T through AI two cells to the right

Then I will do a similar loop through Column AK, with the only
difference being
If the contents of AI are blank "", then I want to shift the contents
of Column U through AJ ONE cell to the right

Here's what I've tried for the Column AJ loop:

Sub MoveBadRows()
'This Sub is not good yet
Range("aj2").Select

Do While Selection.Offset(0, -1) < ""
If IsEmpty(ActiveCell) Then
Range(ActiveCell.Offset(0, -1):ActiveCell.Offset(0,
-16)).Select
Selection.Cut Destination:=ActiveCell.Offset(0,
2).Range("A1:P1")
ActiveCell.Offset(1, 0).Select

Else: ActiveCell.Offset(1, 0).Select
End If
Loop


End Sub

I know that my problems are :
1) In defining the range that is to be moved
2) Getting the active cell back a single cell in column AK

AI AJ AK
0 0 0
0
0 0 0
0 0
0 0 0
0
0 0 0
0 0
0 0 0




Note:My download always has 37 columns (AK) and varying number of rows.

Thanks!

Looping through rows and shifting certain contents
 

OK, I dug in a little more and came up with this solution. It's not a
super fast calculation, but it works faster than I could manually. I
may add something to it to only do the calcs on filtered rows or
something.

Sub MoveBadRows()

Range("aj2").Select

Do While Selection.Offset(0, -1) < ""

If IsEmpty(ActiveCell) Then
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0,
-16)).Select
Selection.Cut Destination:=ActiveCell.Offset(0,
2).Range("A1:P1")
ActiveCell.Offset(1, 16).Select

Else: ActiveCell.Offset(1, 0).Select
End If
Loop

Range("ak2").Select
Do While Selection.Offset(0, -1) < ""

If IsEmpty(ActiveCell) Then
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0,
-16)).Select
Selection.Cut Destination:=ActiveCell.Offset(0,
2).Range("A1:P1")
ActiveCell.Offset(1, 16).Select

Else: ActiveCell.Offset(1, 0).Select
End If
Loop


End Sub

Looping through rows and shifting certain contents
 

I'm trying to think of how you could do it a little quicker. But I haven't
developed the code, but the idea is that it looks like both routines have all
of the same code. So it doesn't matter if you are looking at AJ or AK it
does the same thing. Therefore
you should be able to look at the first cell, do the work you need, if there
is work. Move to the right one cell, do the work you need, then increment
down and to the left however many spaces you have moved to the right. (Might
include some additional variables to keep track of the iterations.) Thus,
you start at Range AJ2 and move to AK M where M is the last row.

Maybe a little cleaner, but if whatcha' got works, good on you.

"Steven" wrote:

OK, I dug in a little more and came up with this solution. It's not a
super fast calculation, but it works faster than I could manually. I
may add something to it to only do the calcs on filtered rows or
something.

Sub MoveBadRows()

Range("aj2").Select

Do While Selection.Offset(0, -1) < ""

If IsEmpty(ActiveCell) Then
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0,
-16)).Select
Selection.Cut Destination:=ActiveCell.Offset(0,
2).Range("A1:P1")
ActiveCell.Offset(1, 16).Select

Else: ActiveCell.Offset(1, 0).Select
End If
Loop

Range("ak2").Select
Do While Selection.Offset(0, -1) < ""

If IsEmpty(ActiveCell) Then
Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(0,
-16)).Select
Selection.Cut Destination:=ActiveCell.Offset(0,
2).Range("A1:P1")
ActiveCell.Offset(1, 16).Select

Else: ActiveCell.Offset(1, 0).Select
End If
Loop


End Sub

Looping through rows and shifting certain contents
 

GB

Thanks.

You're totally right. My macro does take a while to run through all of
the rows. I'll give some further thought to your suggestions.

Steve