• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto Delete Rows

N

nnavarro

Member
Auto Delete Rows

I have a huge sheet (data dump) in which I keep data for many companies “20”. My manual process is as follows: (1) Filter in column A and delete all data called “indirect”. This leaves me with rows called “direct” only. (2) Filter in column B and delete companies not equal to “data selected eg. Union”. I do this by using a drop down validation list that is referenced from another page. After this, I am left with “direct” data for company “union” only.

I do this activity for 20 companies at least 45 times per month. I would like to automate this but don’t know enough to create a macro. Can someone help me create code to do this activity with the push of a bottom?


Thanks,

nnavarro
 
Hi nnavarro,


I hope you will get solution at http://chandoo.org/forums/topic/macro-needed-to-remove-rows-based-on-predefined-condition


I asked this few days back.


Regards,
 
Hi, mnavarro!


Give a look to this file:

https://dl.dropbox.com/u/60558749/Auto%20Delete%20Rows%20%28for%20mnavarro%20at%20chandoo.org%29.xlsm


Regards!
 
Hi SirJB7 & Kuldeep,


This is so awesome! How did you come up with this? Thanks for the tip. I really enjoyed it! My problem is a bit different. Let’s say that I get a file from my supervisor every day with sales data from every country, but I am only interested in the US, UK, & Canada. I have a data validation list at the top of the sheet with these three options. The manual process is (1) Filter by country and deselect the US. (2) Go back and delete all rows that are still showing (other than US). (3) Deselect the US. (4) Applied another filter. Let’s call it region 1. (5) Deselect region 1. (6) Go back delete all rows (other than region 1). (7) Deselect region 1. This is nice because now I only have

Can these two options be combined into one macro? For instance, if I get a new data dump later on that day but now I only wanted to run it for Canada. Can I get data for Canada region 2 only?


Although I said auto delete. I think this is a select delete the rest. It starts by dumping data into a sheet in which data never changes. I select the company (from a list) + another criteria (eg. keep only direct)and delete the rest.


Thanks for the help.


nnavarro
 
Good evening nnavarro

Probably not understanding the question but can you not turn your data in to a table then you can filter to your hearts delight
 
Hi Bobhc!


Yes, that is what I normally do but because I do it for 24 companies twice a day it can get really heactic. The worst is when I send the file to others. You won't believe all the issues and tie-out problems. If a macro could allow me to select a company delete all other companies and select a criteria "region". That would be terrific
 
nnavarro I can understand that for that amount of data perhaps a macro/vba would be better

I have done it by click on the table filters to show what I want and then copy/paste to a new workbook and then email to all concerend
 
Hi Bobhc!


That is exactly what I am talking about. Do you, SirBJ7, or Kuldeep can share code with me? I will be happy to share my book with you to give a better idea of what I am trying to accomplish.


Thanks,


NN
 
Hi, mnavarro!

Give a look at the second green sticky post at this forums main page for uploading guidelines.

http://chandoo.org/forums/topic/posting-a-sample-workbook

Please include detailed examples and manual output if applies.

Regards!
 
Hi SirJB7,


Thanks for the tip. I have uploaded my file. In my file I have two tabs. 1) data dump 2) data dump two. I included arrows to show the columns that I am filtering on. The data dump 2 includes the end result.


Thank you for your help.


NN


https://www.dropbox.com/sh/9l8gri2luex4dse/IXccSvVInN?m
 
Hi SirJB7


I was wondering if you wouldn't mind giving a hand with this macro. I really think that it would be a really nice thing to have.


Thanks,


NN
 
Hello,

I am posting from a tablet hence cannot look at your file, but from what I understand advanced filter can do the job for you. Just select the range and add company name as criteria. Filter in place or a new worksheet.
 
Hi, mnavarro!


I took your file, added my uploaded file's worksheet Parameters with two new parameters (Input sheet and Output -optional- sheet), adjusted the comparison criteria to your pointed A and I column, copied the macro code into your 'Data Dump Before' sheet duplicated for testing as 'Data Dump New', added a new sheet as Output parameter value 'Invalid Data', updated the code to the new parameters, and nothing more.


Then I give a try to test if it worked fine, and it did. But too slow (+1h). So I then changed deleting rows one by one method to deleting rows once based on filtering method. And it runs acceptably well (6-7min). You can try by yourself with the macros DeleteRowsUponCriteria_RowByRow and DeleteRowsUponCriteria_Filter respectively.


Here's the link to the new file, just advise if any issue.

https://dl.dropbox.com/u/60558749/Auto%20Delete%20Rows%20-%20Band%20Rate%207-7%20%28for%20mnavarro%20at%20chandoo.org%29.xlsm


Regards!
 
Hi SirJB&!


Thank you so much for your help. It is so cool. I tried running it and it takes a little bit. I compered it to what I do manually and realized that I do a two sorts one to delete the company not selected and one to delete the indirect items. Other wise it takes a really long time to delete all the other data. So sorting before deleting is key. I tried looking at the code but you are centuries ahead of me. Can I bother you again and see if you could sort before deleting on the macro?


Thanks,


NN
 
Hi, nnavarro!

I noticed that processing time wasn't the ideal (I run it on an i7-920, 6Gb 2000MHz, and took 6-7 min). The two sorts: first one is on column A only, and second on column I only? Or both have more criteria?

Regards!

PS: I always wrote your name wrongly or you changed first m to n? Just curious...
 
Hi SirJB7,


LOL. Don't worry about the name. That is actually funny.


Yes, just two filters column A="Company Code" & I'"Inderect". It was crazy when I filter and delete it took about two minutes. If I tried to delete with the filters on it took about 12-15 minutes. I wish I would have realized this before you did all that work.


Thanks,


NN
 
Hi, nnavarro!


I didn't forget about it, even it looks like if I had. In fact I forget to post that you could download again the file from same previous link. It's updated with you suggestion of the two sorts, and now it runs in 10 seconds. Not bad at all, isn't it?


I've created a new clean sheet, same name 'Data Dump New', and a backup too for test purposes only. Why? Because originals Before and After both had a structure that didn't let use the Autofilter object/property directly, having to use instead the ListObject(1).Range.Autofilter, which make it slower the process of deleting rows, whether sorted or not.


If you happen to find any error while applying to your real file or data, please advise and upload the original workbook so as to find a workaround.


Regards!
 
Hi Sir JB7!


The macros is great it is so fast. I tested it over and over and it worked every single time. I really had a great time with it. I made a little changes in the parameter data. I linked it to the summary tab so if I change the company it will automatically change.


I also deleted some tabs just to keep it nice and clean. I added a button so I can run it with a click. But now I keep getting an run error. I don't know how to fix it. Can I ask you one more time to help me. I really appreciate.


Muchas Gracias,


NN


https://dl.dropbox.com/u/90166573/Band%20Rate%207-13.xlsm
 
Hola, nnavarro!


The problem is that you're building your sheet 'Data Dump New' with tables (Formula tab, Named Ranges group, Names Administration icon, see Table1 definition).

When you use tables, you can' use 'Selection.Delete Shift:=xlUp' statement as an error arises that says "No se pueden desplazar las celdas de una tabla o un rango filtrado." ("Table's or filtered range's cells can't be moved/displaced.").

That was why I sent you the worksheet with no tables (previous was 'Table15' or something like that.

Can you delete your worksheet and build it again without tables? Or maybe adapt mine from uploaded file and format and populate it as needed?

I can do it for you again, but if you change it another time, we'll be in the same situation.


De nada, amigo!


Saludos!
 
Hi, nnavarro!


Here's the link to the clean file with original sheet named as 'Data Dump New Old' and new working sheet named as 'Data Dump New'. Used an ActiveX command button control instead of a shape with macro assigned (change if you want, I prefer controls that respond to normal events and not to assigned macros):

https://dl.dropbox.com/u/60558749/Auto%20Delete%20Rows%20-%20Band%20Rate%207-13%20%28for%20nnavarro%20at%20chandoo.org%29.xlsm


If you happen to change it and rebuild or copy or make something that creates a ListObject object (table for sure), please write down the steps so as to discover what to change. Otherwise, use my version and forget about your strange, obscure, dark, evil, and ...<place here a lot of ugly adjectives>... methods. :p


Regards!
 
Hi SirJB7,


I decided to go with your version and not to mess with my evil, obscure version. As you suspected I added a table so formuals and rows get copied. When I try running the macro I get the following error:


Runtime error "1004' Auto filter method of range class failed.


Can you give me a hand.


https://dl.dropbox.com/u/90166573/Auto%20Delete%20Rows%20-%20Band%20Rate%207-13.xlsm


Thanks,


NN
 
Hi, nnavarro!


Sorry to say, but you still have a Table3 named range corresponding to a Table which leads you again to a ListObject (Formula tab, Names group, Names Admin icon, ...). Can you tell me what you've done starting from my uploaded file to your uploaded file? There is/are operations, procedures, macros (internal or external), or whatsoever might be, but there is/are!


Regards!
 
Hi Dr. SirJB7,


I noticed that you helped so many people. I am sure everyone appreciates that. Once I have deleted the unecesary tab, this is what I do:


1- Create a table. I remove the filters. Select columns a:p rows 1 & 2. Once this info is selected I go to the insert tab and select table. I re-type the formulas. After that any entry I do to the table will copy the formulas.


2- I run the macro. I keep getting an error. This is where I keep getting the error 1004.


Do you have any sugestions. Am I doing something wrong? I hope you can help me.


Thanks SirJB7.
 
You must log in or register to reply here.
Back
Top