+ Reply to Thread
Results 1 to 16 of 16

Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

  1. #1
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Question Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    Hi folks Good morning, Good afternoon or Good evening to all of you......

    I am new to VBA coding, i have been assigned a project where i have to clean up the data and do valuation on it. but the data size is every huge like i have around 2000 rows in each sheet i have 15 sheet with similar pattern of data but with slight changes in each sheet.
    1) I want all the blank rows and all the rows with unwanted data (like R1 to R6) like security,secu,-----,uds, all of them should get filtered and get deleted,but "before deleting also excel should ask whether it is ok to delete this data or not and when i click on ok it should get deleted"

    2) I want all the rows with value equal to 0.00001 in column 7 and column 8 should get filtered out and get deleted,but "before deleting also excel should ask whether it is ok to delete this data or not and when i click on ok it should get deleted"

    3) I want all the rows with values which starts with "UBS" in column 4 should get filtered out and get deleted,but "before deleting also excel should ask whether it is ok to delete this data or not and when i click on ok it should get deleted".

    i want this all this happen in sequence and apart from above conditions rows i don't want to miss even one row please.

    Note : there is a slightest difference in sheet 1 and sheet 2 "UBS" is not there in sheet 2 ,please frame a code for all sheets in such a conditions that the macro doesn't get struck if one of the above conditions are not there in any of the sheet.

    I have attached the workbook with sample sheets (Sheet 1 and sheet 2) . the data is huge(around 2000 rows) and sheets are around 15 with 1 or 2 or all above conditions in the sheet.

    Thanks for your time and effort. your work will be much appreciated......... thanks in advance....




    Please share your results after testing on my sample sheet.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    instead of code just deleting why not have a code to highlight instead....
    because it will ask you if you want to delete but you wont be able to see what its asking you to delete?
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    one code to color
    one code to delete

    though you could really just delete it manually quite easily by sorting by color
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    Hi humdingaling...... I appreciate your effort .... that for your attempt..

    But the macro is not running continuously its just stopping after coloring unwanted data . do i need to run both the codes twice. i want a pop up where when the unwanted data gets colored it should get filtered and a pop up should come so that excel should ask me whether it is ok to delete or not.

    and this macro didn't worked for sheet 2 please give a code where it will work for both the sheets.


    Thanks for your effort again........

  5. #5
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    macro tested to work on both sheets

    you are meant to run Boddulus code then run Colordelete

    you want the code to stop do you can say yes to delete without checking what it going to be deleted?
    reason i seperated into two codes is because it will highlight the code for you so you can view to see if it is ok to delete
    after you have checked everything is ok then you can run colordelete macro

    otherwise if pop up asks you, you wont be able to see/check if its ok without stopping the macro

  6. #6
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    yes macro is working for both ... Thanks for that.....

    actually yes i meant to run Boddulus code and then run colordelete code.

    But my concern is i have to click for the first macro to run , that's fine it has colored unwanted cells but again in have to take cursor on Sub colordelete() and again the have to click on run.... i doesn't want this.

    I meant when i click on run it has to color unwanted cells and it should stop there so that i can check the data after that if i click on run again it has to run second macro rather than again i doesn't want again to take cursor on Sub colordelete().

    Please make this... it will be a great help from u..... thanks in advance.....

  7. #7
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    what i am saying is if msgbox shows you cant check the data to see if what is highlighted is correct
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    Ok i understand your point now thanks......

    can i create a command button for this so that when i hit on command button for first time it should color the unwanted cells and when i hit on command button for 2nd time it should delete rows .
    thanks for your help..........

  9. #9
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    can i please know how did you get that break in between Sub Boddulus() and Sub colordelete() i mean that line between end sub and Sub colordelete() because i have to write some more formulas after deleting of unwanted rows so i want to give a break after that also ...... thanks in advance......

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    Quote Originally Posted by boddulus View Post
    Ok i understand your point now thanks......

    can i create a command button for this so that when i hit on command button for first time it should color the unwanted cells and when i hit on command button for 2nd time it should delete rows .
    thanks for your help..........
    assigning macro to button
    http://office.microsoft.com/en-au/ex...010236676.aspx

    i am not here to do it for you just help you learn

    recommend you use form instead of activex its easier to assign macro
    Last edited by humdingaling; 09-04-2014 at 02:11 AM.

  11. #11
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    Quote Originally Posted by boddulus View Post
    can i please know how did you get that break in between Sub Boddulus() and Sub colordelete() i mean that line between end sub and Sub colordelete() because i have to write some more formulas after deleting of unwanted rows so i want to give a break after that also ...... thanks in advance......
    use the first version

  12. #12
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    ok thanks for your help i really appreciate your effort.... thanks buddy......

  13. #13
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    can you use column 3 instead of column 11 for your first rule since in other sheets "*" all not there for all data........ Can you refer blank cell and cells with "---*" and cells containing "security".

  14. #14
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    Quote Originally Posted by boddulus View Post
    can you use column 3 instead of column 11 for your first rule since in other sheets "*" all not there for all data........ Can you refer blank cell and cells with "---*" and cells containing "security".
    this does not clear all the rows you want

    rows in red are now are with your new rule
    Attached Files Attached Files

  15. #15
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    added 4th rule for empty data in column 1 and 4 which colours purple
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    08-28-2014
    Location
    india
    MS-Off Ver
    2007
    Posts
    92

    Smile Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    Thank you very much for your replies ....it really help me a lot ... thanks buddy.

  17. #17
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Can some one please help me with V.B.A code to remove the unwanted data in the sheet.

    not a problem
    thanks for the rep

    Cheers
    Hum

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro Remove Unwanted Data
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-14-2013, 06:01 AM
  2. Formula to remove unwanted data & rearrange last name & first name in a cell
    By rbecker69 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2013, 03:12 AM
  3. [SOLVED] Need macro to remove unwanted data from large tables
    By s4driver in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 04-26-2013, 08:07 AM
  4. [SOLVED] Deleting unwanted data from a sheet.
    By Christopherdj in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-03-2012, 10:44 PM
  5. remove unwanted data
    By ahmedalhoseny in forum Excel General
    Replies: 1
    Last Post: 06-14-2010, 04:51 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1