VBA required to delete some rows in each workbook

  • Hi Ozgrid Friends,

    i m on seeking help regarding a manual task which i am doing..

    here my task is like....i have Folder, which contains many workbooks, each workbook contains a

    sheet called START, here in this START sheet, i need to checkout the B column

    for the specific Word/ Approximate match "Not Required:"

    if this word comes in B column, including that ROW & below ROWS has to be deleted from that sheet.

    the same has to be done for all the workbooks in that folder.

    in the attached sample workbook i have highlighed the ROWS in yellow, which has to be deleted.

    Requesting someone Expert to help me on this Task.sample book.xlsx

  • Try this. You might want to change delete to select whilst testing.

    Also, change the directory path in the code

  • Sir, i have tried with your code, but its not happening, its opening the first file from the path & getting stopped.

    could you please have a look on it.

  • Try this, I haven't time to check it but it should work

  • my badluck :(

    nothing happening Sir,

    Just a message window appear & says "Done" but nothing gets changes in workbooks

    Please have a look on it Sir.

  • Try this, it works for me. You ill be prompted to pick the folder then it will run through each workbook

  • Hi Sir,

    Few things to get clarified.....

    1) if i need to add one more word to check , like "Not Required" & "Not checked"...How to do that?

    2) some of the sheet names are slightly different, instead of "START" it is updated as "START "

    a space at the last makes the code to get stopped there...please help me to resolve it Sir

  • Actually try this

    You have probably noticed that you are getting a privacy warning, you can fix this using this tip Remove The Personal Information Warning

  • i tried with the updated code also......getting stopped at the location of

    If oWs.Name Like "Start*" Then Set rCl = oWs.Range("B:B").Find(What:="*Not *", LookIn:=xlValues)
  • Sorry, I didn't check properly. I'm busy upgrading my own web site.

    This should work now

  • i m extremely sorry for Disturbing you in middle of your work Sir,

    i tried with the updated code, now no error appears, just running til the end, but there is no changes in any of the workbooks,

    i m attaching the folder for your reciew Sir.

    not req.zip

  • Sir, code almost working fine on all the files, only below things to be added to it.

    1) if i need to add one more word to check , like "Not Required" & "TAT"(this one is newly added)...How to do that sir?

    2) some of the sheet names are slightly different, instead of "START" it is updated as "START "

    a blank space at the end of sheet names making some troubles, is it possible to remove spaces at the end of sheet name.

    FYI...this sheet name (START) always comes at first position.

    Excuse me Sir ..if i m going very lengthy.... can i start the above request in a new post ?

  • Try this

  • Worked Like a Charm :)

    Just Saying Thank You will never repay your kindness Sir.

    i really appreciate your patience in replying to all threads posted in this forum :)

    can you please share your Webpage link Sir.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!