+ Reply to Thread
Results 1 to 14 of 14

Delete Content in a row while opening

  1. 06-28-2011, 02:51 AM #1
    Julian Schubert
    Julian Schubert is offline
    Registered User
    Join Date
    06-21-2011
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    24

    Delete Content in a row while opening

    Hi @ all,

    I have a workbook with several worksheets.
    Now I want that the content in row 'AA' of Worksheet '1' gets deleted everytime when I open up the workbook.
    Please be aware that it should not get deleted everytime I open up the Worksheet '1' when I switch thorugh the worksheet...
    It should only get deleted when I open up the whole workbook for the first time.


    Thank you in advance,
    Julian
    Last edited by Julian Schubert; 07-06-2011 at 08:38 AM.
    Reply With QuoteRegister To Reply
  2. 06-28-2011, 03:15 AM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,975

    Re: Delete Content in a row while opening

    AA is a column, not a row ... is that what you meant?

    You'd use a Workbook_Open event to do this:

    Please Login or Register  to view this content.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 06-28-2011, 03:24 AM #3
    Julian Schubert
    Julian Schubert is offline
    Registered User
    Join Date
    06-21-2011
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Delete Content in a row while opening

    Yes, of course I mean the column - Sorry!

    Little extra: Is should start deleting the whole column starting in row '2' (there is a heading in row 1...)

    Thank you very much.
    Reply With QuoteRegister To Reply
  4. 06-28-2011, 03:30 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,975

    Re: Delete Content in a row while opening

    Maybe then (untested):

    Please Login or Register  to view this content.

    Regards
    Reply With QuoteRegister To Reply
  5. 06-28-2011, 03:41 AM #5
    Julian Schubert
    Julian Schubert is offline
    Registered User
    Join Date
    06-21-2011
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Delete Content in a row while opening

    Hello TMShucks,

    Sorry it does not work -

    maybe it is easier if the code should delete all columns 'AA' (starting with AA2) in all worksheets in the workbook...
    but only if I open up the whole workbook for the first time, not while I am working in the workbook and switching from sheet to sheet...

    Thank you
    Reply With QuoteRegister To Reply
  6. 06-28-2011, 03:53 AM #6
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,975

    Re: Delete Content in a row while opening

    Works for me and does exactly what you have asked for.

    When the workbook is opened, it will clear all cells in column AA from row 2 down.

    Activating and deactivating the sheet will have no effect.

    Regards
    Reply With QuoteRegister To Reply
  7. 06-28-2011, 04:39 AM #7
    Julian Schubert
    Julian Schubert is offline
    Registered User
    Join Date
    06-21-2011
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Delete Content in a row while opening

    hmm... okay maybe it does not work because there are other codes and I tried to implement your code in the wrong position...

    this is the complet code and I put yours just on the top.

    FYI: The worksheet is called "ALL", not "Sheet1" anymore


    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  8. 06-28-2011, 04:56 AM #8
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,975

    Re: Delete Content in a row while opening

    It shouldn't make a difference where in the module it is but it *must* be in the workbook class module, not a standard module. Same with the Worksheet_Activate ... that needs to be in the worksheet class module for the sheet being activated, unless you use the Workbook_SheetActivate event which is in the workbook class module.

    The workbook class module is the module that opens when you double click on ThisWorkbook in the VBE.


    Regards
    Reply With QuoteRegister To Reply
  9. 06-28-2011, 05:05 AM #9
    Julian Schubert
    Julian Schubert is offline
    Registered User
    Join Date
    06-21-2011
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Delete Content in a row while opening

    sorry - I am a rookie

    May I ask you that you adopt your code in mine so that it works?
    I am not able to do it on my own - embarassing

    Thank you in advance.
    Reply With QuoteRegister To Reply
  10. 06-28-2011, 05:21 AM #10
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,975

    Re: Delete Content in a row while opening

    That doesn't look like Rookie code to me ;-)

    See the attached sample workbook. As I don't speak German, I'm not sure if it works and does what you want ... and I don't have the workbook(s) it tries to access.

    But give this a go.

    Regards
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 06-28-2011, 05:57 AM #11
    Julian Schubert
    Julian Schubert is offline
    Registered User
    Join Date
    06-21-2011
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Delete Content in a row while opening

    Thank you for your work.
    It lookes almost very good =)


    Little exception:

    Please Login or Register  to view this content.
    It only deletes the content in shhet "ALL" - and now sometimes I want to change the code that in all worksheets the column AA (starting AA2) gets deleted.

    what do I have to change:

    I tried this code, but it does not work:

    Please Login or Register  to view this content.

    help?
    Thank you


    P.S. "the" code took me forever to make it the way it is... you can not imagine
    Reply With QuoteRegister To Reply
  12. 06-28-2011, 06:03 AM #12
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,975

    Re: Delete Content in a row while opening 

    Please Login or Register  to view this content.

    You need to loop through the worksheets collection, not the workbook.

    Regards
    Reply With QuoteRegister To Reply
  13. 06-28-2011, 07:10 AM #13
    Julian Schubert
    Julian Schubert is offline
    Registered User
    Join Date
    06-21-2011
    Location
    Germany
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Delete Content in a row while opening

    ok looks perfect - thank you!
    Reply With QuoteRegister To Reply
  14. 06-28-2011, 07:26 AM #14
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,975

    Re: Delete Content in a row while opening

    You're welcome.

    If this has answered your question, please mark your thread as Solved. See my signature for details or the FAQ.

    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1