asked on

Micosft Excel/VBA - Clear content of cell when adjoining cells have content

The attached file contains 7 columns.
Column A is Lot number
Column B is Flat Number
Column C is Street Number.

The objective is to clear the content of a cell in Column A, when there is a number or letter on the same row in Column B and Column C

That means when there is a Lot Number, Flat Number and Street Number in the same row, the Lot Number is to be cleared.
Example-of-Lot-Flat-and-Street-numb.xlsx

Subodh Tiwari (Neeraj)

Give this a try...

Sub ClearLotNumbers()
Dim ws As Worksheet
Dim lr As Long
Application.ScreenUpdating = False
Set ws = ActiveSheet
lr = Cells(Rows.Count, 1).End(xlUp).Row
With ws.Rows(1)
    .AutoFilter field:=2, Criteria1:="<>"
    .AutoFilter field:=3, Criteria1:="<>"
    Range("A2:A" & lr).SpecialCells(xlCellTypeVisible).ClearContents
    .AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Open in new window

gregfthompson

ASKER

Thanks - great job.

Can you amend it so that when there is no content in the column C cell (Street Number), the content of the Column A cell is moved to the Column C cell?

Subodh Tiwari (Neeraj)

Right now I am away from the system. Will tweak it once I am back.

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gregfthompson

ASKER

Thanks heaps.

Fabulous piece of work!

gregfthompson

ASKER

Oops - it does not work on all.
It appears to work for some. But in a list of 945000, it did not delete 19 cells in Column A. These were all numbers.

Are you able to include a counter to show how many cells were amended?