Solved

Write a Macro to delete unneeded rows in Excel

Posted on 2014-01-22
20
232 Views
Last Modified: 2014-02-12
I have a spreadsheet that i create each day from a daily report and I want to be able to automactially delete rows that contain certian text.

LIke:

Accounts from List(1 - 100 of 3218)
Account Client name Rebalance
Date
Drift Report Page
https://

Can someone show me what the macro syntax would be so that my user can just run the macroto delete all unneeded rows.
0
Comment
Question by:seamus9909
  • 11
  • 9
20 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39800648
these strings exist in what column ?
Could you post a sample of this workbook highlighting what are the rows that need to be deleted in yellow
gowflow
0
 

Author Comment

by:seamus9909
ID: 39800949
they exist in Column A.

I have attached an example   This file does not contain any confidential data.
Raluca-Example.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39801173
Here it is and here is how it works.

1) download the file and make sure your macros are enabled.
2) I made an exact copy of your Original sheet and called it Original (test) for you to test the macro on it.
3) click on Original (test) sheet and select from the development tab Macro and click on the macro
DeleteSpecificRows

It will remove from this sheet all the undesired rows that you specified and put them in Sheet3 for you to check.
4) You will get a message box at the end telling you how many rows were removed.
5) Check the new Original (test) to see if the data is ok and go check in Sheet3 to see if any rows were removed but should not.

Let me know.
gowflow
Raluca-ExampleV01.xlsm
0
 

Author Comment

by:seamus9909
ID: 39803056
We are getting there.  The Macro did not delete "https;//"  also is there a way that the row can be deleted in its entirely so the results dont include blank rows?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39803309
for sure we can do whatever we want. First let me then move to Sheet3 the ENTIREROW so you examinee it and when all is ok then forget about sheet3.

As far as
"https;//"   (You have put semicolumn ; )

it should be
"https://"    (It should be Column : )

: NOT ;  

Try this version.
gowflow
Raluca-ExampleV02.xlsm
0
 

Author Comment

by:seamus9909
ID: 39803787
Thanks so I have attached the spreadsheet.  There are some things that need to be deleted.

I have highlighted those examples. I thought I coudl jsut add the items to the  
sUnwanted = "Accounts from List(1 - 100 of 3218)|Account Client name Rebalance|Date|Drift Report Page|https://"


Also for "accounts from List(1-100 of 3218).  there are multple rows that start with the strgina Accounts from List(.....  

Is there a way to use a wildcard character so that we could use something like accounts*?
Raluca-ExampleV03.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39803845
It is very nice what you did, but sorry I cannot go over one zillion row to see what you want. Please take the time to put all the
DISTINCT or different sentences and copy them into sheet3 and post the workbook again.

gowflow
0
 

Author Comment

by:seamus9909
ID: 39803906
Sorry


I have put all the distinct in Sheet 3 that will need to be added to the macro.
Raluca-ExampleV03.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39804249
ok here it is

I don't know if my logic is correct but you will have to test this. I used a different approach and it is now user set. I created a worksheet called Unwanted and put there all the sentences or part of sentences that we want to remove. You may add more as you go along and you can test that like put in your file some unwanted stuff and put in the sheet Unwanted part of that stuff and see if it removes it.

Check it an let me know
gowflow
Raluca-ExampleV04.xlsm
0
 

Author Comment

by:seamus9909
ID: 39804458
awesome thanks!!!!!!!!!!
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 29

Expert Comment

by:gowflow
ID: 39804686
Did you try it ? is all ok ?
gowflow
0
 

Author Comment

by:seamus9909
ID: 39807518
so a couple of anamolies that I was wondering how we could handle.  I have included them on the "DoNotWant to Delete" tab.  There are groupings that contain PNC STAR but also contain another key row that would cause me not to want to delete.

Also for the rows that we do delete "can we show the users the count of PNC Star rows that we deleted" and also place all the rows that we delete into a new tab in the Worksheet"?
Raluca-ExampleV04.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39807812
Sorry I do not follow you !!!

The items you put in the Donotwant to delete tab are not deleted by the macro !!!

You want the count of "PNC Star" like all the rows that have this only or this anywhere in the row ?? and you want them in a separate worksheet ? not with Sheet3 ??

You need to be more clear. Please read again what you ask and you will see it is not clear (unless you assume that the other party know what your talking about but it is not !!!)

gowflow
0
 

Author Comment

by:seamus9909
ID: 39809564
Sorry I should be clearer.   So the items that I put on that tab where to highlight that the macro was deleting them, however I would not want them deleted.    This is considered a group, and when the group contains PNC Star and another model I don't want the rows deleted.

20357411028924 SINGER MEDICAL ASSOC P/S 12/27/2013 VF
PNC Star
XLY CONSUMER DISCRETIONARY SELT 32.000% 30.900% 1.100%
Eaton Vance Large Cap Value

When the macro deletes PNC Star I would like a count of how many PNC Stars were deleted.  Just the count of the PNC Star rows.

I would like the rows that were deleted as a result of the MACRO being places in a separated tab  (Not Sheet3).

Hope this clarifies.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39809769
Sorry I am totally lost !!!
NO WHERE IN SHEET UNWANTED THERE IS A SEQUENCE THAT SAY THAT:
PNC Star should be deleted !!!! how do you assume that the macro is deleting it.

You are probably talking about something else that is posted !!!
the below file is the last one I posted and it has no reference to delete any PNC sequence.

the only thing that have pnc is this sequence that you added in the file:
maap.mapmfc.com/pnc/xi.aspx?xi6E675ECF-9219-48A1-A06F-C246B97D0D10=... 1/24/2014

and still it is not 'PNC Star'

Now if you want the count of PNC star sequence that is no problem I have included it in this version and when you say put them in a different tab than Sheet3 that is no problem also I will put them in RowsDeleted sheet.

Pls check it out and appreciate your quick reply so we do not drag on this issue.

FURTHERMORE
I have developed the macro called:
DeleteSpecificRows

but also see that you have there an other macro that was not developed by me and that is called:
DeletePNCSTAR

Maybe this one is causing you all the trouble !!!! ?????
what I did is I commented out this line in the macro DeletePNCSTAR (so it will not delete PNC Star)
'Range("A" & RwIdx - 1 & ":A" & LastNameRw).EntireRow.Delete
and added counters for PNC Star and counter for your item numbers and it give you a message box at the end,




HERE IS HOW TO MAKE IT WORK ON THIS VERSION:
------------------------------------------------------------------------

1) I copied your sheet original (2) to original (3) so you can compare both at the end.
2) Select sheet original (2) then activate the macro
DeleteSpecificRows
when it finshes it will tell you how many rows it deleted and put them in sheet RowsDeleted
3) Check the data deleted in that sheet then point back again to sheet original (2) and run the macro that you had there that I modified that is called
DeletePNCSTAR
4) It will run and will give you a count the first count is how many
PNC Star it found and the second count is how many
10017410187600 JAMES M STRAUB FOR C S GUILBERT 10/31/2013 UY
these type of items that have a number in the beginning it found.

please be clear on what you want and check it out.


gowflow
Raluca-ExampleV04b.xlsm
0
 

Author Comment

by:seamus9909
ID: 39810251
ok I know im confusing you.

I still want the rows that are associated with PNC STAR deleted and moved to a new sheet.  


if we can do that then everything else looks great!

Tanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39810338
Say this again ?
We move all rows deleted in my macro
DeleteSpecificRows
to Sheet3

and
PNC Star to
RowsDeleted
?? is that what you want ???
If yes then in the sheet RowsDeleted you will get say 1500 rows with something like this
PNC Stars
PNC Stars
PNC Stars
PNC Stars
PNC Stars
PNC Stars
PNC Stars
PNC Stars
PNC Stars
PNC Stars
PNC Stars
PNC Stars
....


Is that what you want ??? what is the use of this ?? I am still not clear on what you want as it does not make sence. Maybe something I am missing here. !!!

gowflow
0
 

Author Comment

by:seamus9909
ID: 39812641
for the Macro  "DeletePNCSTAR;,  you delete more than just a rown that says PNCSTAR you also delete : the row starting with 1001.. and any row after the PNCSTAR row until you hit the next row starting with  1001....
As an example.  Your macro deleted the following rows:

10017410187600 JAMES M STRAUB FOR C S GUILBERT 10/31/2013 UY
PNC Star
XLV HEALTH CARE SELECT SECTOR 33.000% 34.020% 1.020%


I would like all of these writtent to a seperate tab.

Make sense?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39813531
ok got what you want finally.

Here is how it work:
1) Download the file and make sure macros are activated.
2) I made a copy of your original sheet and called it original (backup)
3) Point to Original
4) Select from the macros the only macro you will find is:
DeleteSpecificRows
5) Run it, it will put in a first phase all the unwanted occurences in sheet Unwanted in the sheet3 and remove them from sheet Original.
6) It will give you a messagebox with the total items removed and tell you that it will move to step 2 to transfer all the Group PNC Star to the sheet RowsDeleted
7) Here it will activate a sub macro called: MovePNCStarGroup that will do its job and give you a final count.
8) At the end you should endep with an empty sheet Original and a sheet RowsDeleted where all the PNC Star group are there and it will give you a count of all PNC Star occurences found.

Hope that this is what you were looking for.
Let me know
gowflow
Raluca-ExampleV05.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39815725
Any chance to have tried out the proposed solution ?
gowflow
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 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

16 Experts available now in Live!

Get 1:1 Help Now