• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Write a Macro to delete unneeded rows in Excel

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
seamus9909
Asked:
seamus9909
  • 11
  • 9
1 Solution
 
gowflowCommented:
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
 
seamus9909Author Commented:
they exist in Column A.

I have attached an example   This file does not contain any confidential data.
Raluca-Example.xlsm
0
 
gowflowCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
seamus9909Author Commented:
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
 
gowflowCommented:
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
 
seamus9909Author Commented:
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
 
gowflowCommented:
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
 
seamus9909Author Commented:
Sorry


I have put all the distinct in Sheet 3 that will need to be added to the macro.
Raluca-ExampleV03.xlsm
0
 
gowflowCommented:
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
 
seamus9909Author Commented:
awesome thanks!!!!!!!!!!
0
 
gowflowCommented:
Did you try it ? is all ok ?
gowflow
0
 
seamus9909Author Commented:
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
 
gowflowCommented:
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
 
seamus9909Author Commented:
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
 
gowflowCommented:
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
 
seamus9909Author Commented:
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
 
gowflowCommented:
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
 
seamus9909Author Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
Any chance to have tried out the proposed solution ?
gowflow
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now