a macro puzzler

Huhnda01

New Member
Joined
Nov 21, 2005
Messages
2
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

Well-known Member
Joined
Jan 6, 2003
Messages
1,928
if you select the second column of data (where it's checking if the value is 0) then run this macro:

Code:
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

New Member
Joined
Nov 21, 2005
Messages
2
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

Well-known Member
Joined
Jan 6, 2003
Messages
1,928
Did you try selecting the entire column before running the macro?
 

Forum statistics

Threads
1,078,006
Messages
5,337,694
Members
399,162
Latest member
Sabina

Some videos you may like

This Week's Hot Topics

Top