Solved

macro to remove text in a cell based on selection criteria

Posted on 2014-03-19
18
211 Views
Last Modified: 2014-03-20
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
0
Comment
Question by:route217
  • 10
  • 7
18 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39939391
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
 
LVL 4

Expert Comment

by:senthilkumarsb
ID: 39939412
I hope you trying to play with "Filters". explain your question in different way...
0
 

Author Comment

by:route217
ID: 39939454
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39939516
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
 

Author Comment

by:route217
ID: 39939526
Hi gowflow

That correct. .
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39939539
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39939618
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
 

Author Comment

by:route217
ID: 39939697
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39939777
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 29

Expert Comment

by:gowflow
ID: 39939791
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
 

Author Comment

by:route217
ID: 39941913
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39942019
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
 

Author Comment

by:route217
ID: 39942112
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
 
LVL 29

Expert Comment

by:gowflow
ID: 39942124
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
 

Author Comment

by:route217
ID: 39942139
Hi Gowflow

Agree 100 percent with the above and based on those criteria. ..the date still remains.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39942206
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
 

Author Comment

by:route217
ID: 39942214
Hi gowflow
Agree with ur comments. ..any thing u post is 99 percent spot on...

Let me test
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39942450
any news ?
gowflow
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now