VBA Delete Data in cell A1 if B1 <> "Y" , loop through each cell in column A in a given range

[Will85]

I have a list of ever growing data, I can define the start and last row dynamicaly.

I want a macro that will go through each cell in Range("A2:A" & LastRow)

and delete the data in that cell if the corresponding cell in column B does not equal "Y"

Basically, each cell in Column A has an outstanding balance, each cell in Column B Is a drop down data validation List (the only option is "Y").

If a user has marked, for example, B512 as "Y", then do not delete the data in cell A512, otherwise clear the contents.

I want it to loop through each cell in the range and do this.

 

[johnnyL]

How about:

Sub TestCellDelete()
'
    Dim ArrayRow    As Long
    Dim SourceArray As Variant
'
    SourceArray = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)                     ' Save Columns A & B into an array
'
    For ArrayRow = 1 To UBound(SourceArray, 1)                                              ' Loop through the rows of the array
        If SourceArray(ArrayRow, 2) <> "Y" Then SourceArray(ArrayRow, 1) = vbNullString     '   If Column B value <> 'Y' then delete value in column A
    Next                                                                                    ' Loop back
'
    Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row) = SourceArray                     ' Write the array back to the sheet
End Sub

 

[Fluff]

Another option

Sub Will()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(" & .Offset(, 1).Address & "=""Y""," & .Address & ","""")")
   End With
End Sub

 

[Will85]

Another option

Sub Will()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("if(" & .Offset(, 1).Address & "=""Y""," & .Address & ","""")")
   End With
End Sub

Pretty slick, thank you very much. Worked perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.