a macro puzzler

[Huhnda01]

I'm having a difficult time trying to create a macro or even a formula that does what I'm hoping to do. This is my first post here, so I don't know if I can include an attachment or not, so I will try to paste it in.

I would like to run a macro that will take the data from this...

Item A 0 0 0 0 0 0 5 11 20 35 44 67
Item B 0 0 9 10 16 20 22 25 34 36 32 20
Item C 66 68 70 72 75 80 81 80 81 79 78 77
Item D 0 12 20 50 55 63 72 80 88 89 90 92
Item E 20 22 18 15 10 9 7 5 2 1 0 0

to this...

Item A 11 20 35 44 67
Item B 10 16 20 22 25 34 36 32 20
Item D 12 20 50 55 63 72 80 88 89 90 92

To put into words, the first requirement is that the value in the first Cell for each Item must equal zero. If this condition is met, then proceed to step 2. As you can see, Items C & E did not meet the first requirement.

For step 2, if we look at Item A again, it is looking across the Row to determine when it first reaches 10 or more. For this case, it occurs in the 8th cell (value = 11). Once it finds this cell, it takes the value from that cell and the cells that follow and shifts them to the left to the beginning while still keeping the numbers in the same order.

Can this be done with a macro??

Dave

 

[btadams]

if you select the second column of data (where it's checking if the value is 0) then run this macro:

Sub test()
Dim i As Integer

    For i = Selection.Cells.Count To 1 Step -1
        If Selection.Cells(i, 1).Value <> 0 Then
            Selection.Cells(i, 1).EntireRow.Delete
        Else
            Do While Selection.Cells(i, 1).Value < 10
                Selection.Cells(i, 1).Delete shift:=xlToLeft
                'Set cell = ActiveCell
            Loop
        End If
    Next i
End Sub

 

[Huhnda01]

Thanks btadams for writing this code. It does exactly what I want it do to for a given row of data. I have since been trying to figure out a way to get it to automatically go to the next row and repeat the procedure, and then have it go to the next row, and so on.

I'm a novice when it comes to this stuff, but from looking through the VB help, it looks like maybe the For Each...Next statement might be what I need to do? However, I can't seem to figure it out. The reason why I'd like this to repeat automatically is because I will need to run the macro upwards of a 1,000 times per spreadsheet.

Is there a way to have it run the procedure for a row and then have it go to the next row and repeat the procedure all the way down to the end? If an ending is necessary, I think 2,000 rows would be the max number of times it would need to be repeated.

If you don't have time to look into this, I understand. Thanks again!

Dave

 

[btadams]

Did you try selecting the entire column before running the macro?