CODEWITHSUNDEEP
excelexcel-formulavba
A h
A h

Reputation: 3

create a macro to delete cell content in a column that meets certain criteria

Hello can someone help me. I have three columns and I want to search each column and delete content if it meets certain criteria.

Column B delete only cells with names
Columns C delete only cells with the word tray and a number Column F delete only cells with * followed by four 0000 then ends with * This is what I came up with but it isn’t working and I am not sure what to do. I have searched and searched. How do I do this thanks?

column B name column cell format FN,LN Clear any names from row 16 to lastrow. Some 'row may have dates and time I don’t want to touch those

column C range format Tray 226985 clear the content of any rows in that column with the word tray followed by a number to the lastrow

column F range format 000000226985 begins with a * then four 0000 and ends with a * delete cell content with that format to the lastrow

Sub Macro1()

Range(b1, b65536).Value = ("*,*").clearcontent
Range(c1, c65536).Value = tray(*).clearcontent
Range(f1, f65536).Value = ("*0000*").clearcontent

End Sub

Upvotes: 0

Views: 10381

Answers (1)

Simon1979
Simon1979

Reputation: 2108

the below should complete your required task by looping through each cell in column A and checking the offset cells. I have replicated your criteria but I am not familiar with 'tray(*)', I think you may want to replace that line with something like:

If c.Offset(0, 2).Value Like "Tray*" Then

You may need to look into alternatives if it HAS to be a number after 'Tray', I'll leave it to you to get the criteria right but hopefully I have helped with the method of looping.

Dim sh As Worksheet
Dim rng As Range, c As Range

Set sh = ActiveSheet
Set rng = sh.Range("A:A")

For Each c In rng

    If c.Offset(0, 1).Value Like "*,*" Then
        c.Offset(0, 1).ClearContents
    End If
    If c.Offset(0, 2).Value Like tray(*) Then ' see note on replacing this line
        c.Offset(0, 2).ClearContents
    End If
    If c.Offset(0, 5).Value Like "*0000*" Then
        c.Offset(0, 5).ClearContents
    End If
Next c

Upvotes: 3

Related Questions