Results 1 to 4 of 4

Thread: Clearing Data from Specific cols

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    Clearing Data from Specific cols

    I have a macro to clear data from Row 2 onwards in Col A, Col C to I and Col L to O. However, when activating the macro, the data to the right of Col O is also being cleared, which should not be cleared

    Your assistance in resolving this will be most appreciated

    Clear Data.xlsm

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Howardc View Post
    I have a macro to clear data from Row 2 onwards in Col A, Col C to I and Col L to O. However, when activating the macro, the data to the right of Col O is also being cleared, which should not be cleared

    Your assistance in resolving this will be most appreciated
    The "data" in Columns P and T that you say are being cleared are actually not being "cleared"... the cells in those columns contain formulas which rely on data being in Column A... the formulas remain, but are set to display the empty text string ("") when the corresponding cell in Column A are blank (which is what your code line is doing). Are you sure you wanted to include Column A in the cells that you are clearing???

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Hi Rick thanks for getting back to me. The datas in Col a must be cleared

  4. #4
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Howardc View Post
    Hi Rick thanks for getting back to me. The datas in Col a must be cleared
    Then I am afraid Columns P and T will always look like they are blank (they are not as they still contain formulas) until you put data back in Column A. Just so you understand why, this is the array-entered formula you have in cell T2 (as but one example from that column)...

    =IF(A2="","",INDEX(Codes!$C$2:$C$15,MATCH(1,IF(Codes!$A$2:$ A$15=Fassets!$A2,IF(Codes!$B$2:$B$15=Fassets!$E2,1 )),0)))

    The part I highlighted in red is why the cell looks like it was cleared... when you removed the data in A2, this formula displays "" (which looks blank to the human eye). Now, this is the formula you have in cell P2...

    =IF(T2="","",""&T2&"=100")

    Note the part I highlighted in red for it... because the first formula above makes T2 display "", this formula will end up displaying "" also (again, until some data is put back into Column A). So, your original question is not really a problem since all formulas are doing what they were designed to do.

Similar Threads

  1. Replies: 3
    Last Post: 06-10-2013, 06:12 PM
  2. Replies: 6
    Last Post: 06-01-2013, 03:24 PM
  3. Macro to copy data in specific Columns
    By Howardc in forum Excel Help
    Replies: 0
    Last Post: 04-19-2013, 10:42 AM
  4. Replies: 9
    Last Post: 12-04-2012, 09:45 PM
  5. Clearing UsedRange Of WorkSheet Programmatically
    By tfurnivall in forum Excel Help
    Replies: 1
    Last Post: 12-04-2012, 09:05 AM

Posting Permissions

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