+ Reply to Thread
Results 1 to 7 of 7

Clear contents of a cell of row A, if cell in row I is blank

  1. #1
    Registered User
    Join Date
    04-11-2009
    Location
    Toronro
    MS-Off Ver
    Excel 2003
    Posts
    15

    Clear contents of a cell of row A, if cell in row I is blank

    Hi everyone,

    Hope all is well. A quick and sincere thank you, for all the assistance I have received from this Forum.

    I am asking for assistance once more, could someone please assist me with a project I am working on?

    I need to clear the contents of the cell in Row "A", if the cell in Row "I" is blank.

    The issue, is there are roughly 1200 excel documents in a folder and subfolders. That is a painful amount of opening workbooks, sort ascending and deleting. Not on my top 10 list of things to do.

    If someone could create a code to do this, and loop it through a folder (I can move all the files to a single folder manually, not an issue), I would be greatly appreciative.

    I can provide a mock sample if required.

    Thanks in advance.
    Last edited by salventuro; 05-07-2009 at 01:54 PM.

  2. #2
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161

    Re: Clear contents of a cell of row A, if cell in row I is blank

    You didn't say which cell or cells, i assume you mean the cell Ix where x will be the same as the x in Ax

    If thats the case put this in your worksheet code module:
    Please Login or Register  to view this content.
    Not all forums are the same - seek and you shall find

  3. #3
    Registered User
    Join Date
    04-11-2009
    Location
    Toronro
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Clear contents of a cell of row A, if cell in row I is blank

    Hi Simon,

    Thanks for the response. Sorry, I left this out, but yes, the same line.
    i assume you mean the cell Ix where x will be the same as the x in Ax
    I pasted the code in the module as below,

    Please Login or Register  to view this content.
    When I run the code from the VB editor, I get prompted for a Module name.
    No matter what I enter, I get this in the module.

    Please Login or Register  to view this content.
    and it won't work, nothing happens.

    Also, when I try to add you code to another code, (to run in all excel files in a folder, as below,

    Please Login or Register  to view this content.

    I get "Compile Error" message box.

    This has worked for two other codes, they run in all the excel doc's in a folder.

    I have limited knowledge in VBA and am probably missing the obvious, for this I apologize. Any guidance to resolve this would be greatly appreciated.

    Thanks again Simon.

  4. #4
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161

    Re: Clear contents of a cell of row A, if cell in row I is blank

    Firstly i'm not going to solve the problem with your other code until your original question is solved. I supplied you code that worked perfectly, you changed the name of the sub and didn't follow my instruction.

    Leave the code as i supplied it and paste it in the worksheet code module for the sheet you are working with, it doesn't need running manually it will just work when you remove a value from column a the correspond ing cell in column I will have its value deleted!

  5. #5
    Registered User
    Join Date
    04-11-2009
    Location
    Toronro
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Clear contents of a cell of row A, if cell in row I is blank

    Hi Simon,

    Thanks again. Sorry buddy, it is my fault for not explaining this correctly. I now understand what you mean.

    I explained what is needed incorrectly. The excel doc is generated, I do not need to delete any cell values manually. The cell values in column "I" are blank because information is missing from the database the excel doc's are being generated from.
    What is needed, is a code to look in every cell in column "I", if it is empty, then clear the contents in the corresponding row's cell "A".

    Sorry again for the confusion and wasting your time.

  6. #6
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161

    Re: Clear contents of a cell of row A, if cell in row I is blank

    This will do what you need:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-11-2009
    Location
    Toronro
    MS-Off Ver
    Excel 2003
    Posts
    15

    Re: Clear contents of a cell of row A, if cell in row I is blank

    Simon,

    I am in awe.

    Exactly what I was looking for.
    I added your code to the other code, and it runs flawlessly in all workbooks in the folder.

    Thank you again for time, and sorry again for not being very clear on my first post.

    Have a great day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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