Solved

macro to remove text in a cell based on selection criteria

Posted on 2014-03-19
18
215 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
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.

 
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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

828 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