macro to remove text in a cell based on selection criteria

Hi Experts Excel 2007

Need a macro to do the following. .....I currently have filters applied to my header row, row10.

so if I select from the drop down col DE10 "account" and col DF "account" and the text in col DI - rows 10:15000 contain the word closed or time out then blank cell and also blank corresponding cell in col DK
route217Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
gowflowConnect With a Mentor Commented:
Verry weired.
pls note the words:
account
closed
time out

time out is evaluated as whole tike as it is 'time out' (space between time and out)

anyway pls try this version:

Sub BlankCells()
Dim WS As Worksheet
Dim MaxRow As Long, lcount As Long, I As Long

With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False
End With

Set WS = ActiveSheet
MaxRow = 25000

For I = 11 To MaxRow
    If IsDate(WS.Cells(I, "DJ")) And _
        ((InStr(1, LCase(WS.Cells(I, "DE")), "account") <> 0 And InStr(1, LCase(WS.Cells(I, "DF")), "account") <> 0) And _
        (InStr(1, LCase(WS.Cells(I, "DI")), "closed") <> 0 Or InStr(1, LCase(WS.Cells(I, "DI")), "time out") <> 0)) Then
        WS.Cells(I, "DJ") = ""
        lcount = lcount + 1
    End If
    
    If InStr(1, LCase(WS.Cells(I, "DE")), "account") <> 0 Then
        WS.Cells(I, "DE") = ""
        lcount = lcount + 1
    End If
    
    If InStr(1, LCase(WS.Cells(I, "DF")), "account") <> 0 Then
        WS.Cells(I, "DF") = ""
        lcount = lcount + 1
    End If
    
    If (InStr(1, LCase(WS.Cells(I, "DI")), "closed") <> 0 Or InStr(1, LCase(WS.Cells(I, "DI")), "time out") <> 0) Then
        WS.Cells(I, "DI") = ""
        lcount = lcount + 1
    End If
    
Next I


With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
End With


MsgBox ("A total of " & lcount & " cell(s) were blanked out as found to meet all requested criteria")

End Sub

Open in new window



gowflow
0
 
gowflowCommented:
Sorry it is now the fith time I read your post and cannot understand what you want. Could you clarify more ?

Also could you post a small sample of data it would help understanding what you want ?
gowflow
0
 
Senthil BCommented:
I hope you trying to play with "Filters". explain your question in different way...
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
route217Author Commented:
Hi Expert's

Ignore the filters......Apologies for the poor wording.

From the top.
Row 10 headers
If col de (data range row10:25000) has the word "Account" and also col df has the word "Account" then look in col Di and if the same row (assume row 11). Has text with the word closed or time out in it, then blank row 11, col di and like wise if in col dj row11 we have a date (format) 02/03/2014...blank that too.
0
 
gowflowCommented:
so you want a macro for that ? or you want to know what filter to use this ?

I presume you want macro as you stated this right in the beginning of your post


Hi Experts Excel 2007

Need a macro to do the following. .....I

But presser to ask !
gowflow
0
 
route217Author Commented:
Hi gowflow

That correct. .
0
 
gowflowCommented:
so to summit up we are checking for different criterias but the columns involoved are:

1) DE, DF, DI, DJ

2) words to check:
'Account'
'closed'
'time out'
a date (2/4/2012 etc..)

are the words to be checked caps sensitive ? like account or ACCOUNT or Account ... and time out has a space ?
pls confirm

gowflow
0
 
gowflowCommented:
Also here is some more confusing posts:

1) I read your original post and you mention DK whereas after no mention of DK !! so what is it ?l

2) You say if the cell contain the word 'time out' blank that cell
What do you mean by blank that cell ? did you mean Hide the row ??

gowlfow
0
 
route217Author Commented:
Gowflow

The column in tbe second post are correct..ignore first poorly worded post.

Point 2. When I mean blank that cell - I mean remove any text in that cell...so its empty.
0
 
gowflowCommented:
ohhhhh !!

remove data in the cell. so the criteria is independent for each ???

like if DE has account in it like ('Account delivery' or 'this account is big' etc...) we then make this cell blank
and if the next is met then also next cell is blank

or all criterias need to be met to blank all the cells ?

gowflow
0
 
gowflowCommented:
ok here is the code for that, BUT WARNIING !!!! prior to using this code please make a copy of your sheet as a backup as you mentioned 25000 rows and columns till DJ which is a lot of data so incase something wrong with the routine so you can have a backup copy.

I assumed that all criterias are independent like when the criteria is met the cell is blanked out.

Sub BlankCells()
Dim WS As Worksheet
Dim MaxRow As Long, lcount As Long, I As Long

Set WS = ActiveSheet
MaxRow = 25000

For I = 11 To MaxRow
    If InStr(1, LCase(WS.Cells(I, "DE")), "account") <> 0 Then
        WS.Cells(I, "DE") = ""
        lcount = lcount + 1
    End If
    
    If InStr(1, LCase(WS.Cells(I, "DF")), "account") <> 0 Then
        WS.Cells(I, "DF") = ""
        lcount = lcount + 1
    End If
    
    If (InStr(1, LCase(WS.Cells(I, "DI")), "closed") <> 0 Or InStr(1, LCase(WS.Cells(I, "DI")), "time out") <> 0) Then
        WS.Cells(I, "DI") = ""
        lcount = lcount + 1
    End If
    
    If IsDate(WS.Cells(I, "DJ")) Then
        WS.Cells(I, "DJ") = ""
        lcount = lcount + 1
    End If
Next I

MsgBox ("A total of " & lcount & " cell(s) were blanked out as found to meet all requested criteria")

End Sub

Open in new window



Let me know your comments.
gowflow
0
 
route217Author Commented:
Hi Gowflow

Based on initial test the macro work fine less one minor adjustment. .

Only delete date in col DJ if DE and DF are account and or DI has closed or time out otherwise leave dates in col DJ.
0
 
gowflowCommented:
ok try this version

Sub BlankCells()
Dim WS As Worksheet
Dim MaxRow As Long, lcount As Long, I As Long

Set WS = ActiveSheet
MaxRow = 25000

For I = 11 To MaxRow
    If IsDate(WS.Cells(I, "DJ")) And _
        (InStr(1, LCase(WS.Cells(I, "DE")), "account") <> 0 And InStr(1, LCase(WS.Cells(I, "DF")), "account") <> 0) And _
        (InStr(1, LCase(WS.Cells(I, "DI")), "closed") <> 0 Or InStr(1, LCase(WS.Cells(I, "DI")), "time out") <> 0) Then
        WS.Cells(I, "DJ") = ""
        lcount = lcount + 1
    End If
    
    If InStr(1, LCase(WS.Cells(I, "DE")), "account") <> 0 Then
        WS.Cells(I, "DE") = ""
        lcount = lcount + 1
    End If
    
    If InStr(1, LCase(WS.Cells(I, "DF")), "account") <> 0 Then
        WS.Cells(I, "DF") = ""
        lcount = lcount + 1
    End If
    
    If (InStr(1, LCase(WS.Cells(I, "DI")), "closed") <> 0 Or InStr(1, LCase(WS.Cells(I, "DI")), "time out") <> 0) Then
        WS.Cells(I, "DI") = ""
        lcount = lcount + 1
    End If
    
Next I

MsgBox ("A total of " & lcount & " cell(s) were blanked out as found to meet all requested criteria")

End Sub

Open in new window



gowflow
0
 
route217Author Commented:
Hi gowflow

The macro run thorough with no end result. ..the dates are still in colDJ and closed and time out remain in col DI
0
 
gowflowCommented:
MY FREIND !!!!!

we have a communication problem:

The date will be deleted only if:

IF DJ is a DATE
AND
DE has the word account
AND
DF has the word account
AND
(DI has the word 'closed'  OR DI has the word 'time out')

when all these are met then the date is deleted. I did it exactly as you explained it. now if the AND / OR are different let me know.

gowflow
0
 
route217Author Commented:
Hi Gowflow

Agree 100 percent with the above and based on those criteria. ..the date still remains.
0
 
route217Author Commented:
Hi gowflow
Agree with ur comments. ..any thing u post is 99 percent spot on...

Let me test
0
 
gowflowCommented:
any news ?
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.