If A??=1 then copy F?? to L??-1 1

[likelylad]

I have an excel spreadsheet that contains a macro that will delete rows that do not contain the specified criteria.

What I would like to do is to add the following:
1)It will loop through all records
2)It will look at the cells in column A.
3)If the value in the cell is equal to 1 then it will copy the contents of the cell in column F to a cell in Column L on the previous row
4)It will then delete the row with 1 in column A

EG:
1)It is looping through all records
2)It finds the number 1 in cell A10
3)It copies the contents of cell F10 to L9
4)It then deletes row 10

Please Note:
a)Cell A1 will never have the value 1 in it
b)If it reaches an empty cell, the program is to stop.

Thanking you in advance for any help given

 

[dwilson01]

This looks like it will work.

Dim oCell As Object, colF As Integer, colL As Integer, iRow As Integer

colF = 6  'Column F
colL = 12 'Column L

For Each oCell In ActiveSheet.UsedRange.Columns(1).Cells
skipindex:
  If oCell = 1 Then
    Cells(oCell.Row - 1, colL) = Cells(oCell.Row, colF)
    iRow = oCell.Row
    oCell.EntireRow.Delete
    Set oCell = Cells(iRow, 1)
    ActiveSheet.UsedRange
    GoTo skipindex
  End If
Next oCell

Let us know.

Dave

 

[likelylad]

Hi Dave

Thanks very much for your help again.
Worked perfect.