# a macro puzzler

#### Huhnda01

##### New Member
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

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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``````

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

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

Replies
5
Views
484
Replies
4
Views
266
Replies
6
Views
318
Replies
4
Views
205
Replies
10
Views
335

1,203,649
Messages
6,056,544
Members
444,872
Latest member
agutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back