+ Reply to Thread
Results 1 to 7 of 7

Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    San Francisco, Californa
    MS-Off Ver
    Excel 2010
    Posts
    35

    Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation

    *Hi All.

    I have a VBA code that selects multiple worksheets to insert new rows. It then drags functions down to the new rows from a previous row above to keep data validation format.

    Every time this macro is ran, the number of rows and the range of insertion will differ. How would I go back to cells D through F, J, O and R of newly added rows to clear the contents?
    Sample_Workbook.xlsm
    I have attached a sample workbook for viewing.

    Thanks you in advance.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation

    Try something like this...

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-12-2012
    Location
    San Francisco, Californa
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation

    Thanks AlfaFrog. The code is not selecting all the sheets All_Fund_Series, MAPMG, and SCPMG to be modified at onces as needed. It's only applying it to the All_Fund_Series sheet. Is there a reason why?

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation

    You cannot do a .Find on all the sheets at one time.

    Use the For-Next to loop through the sheets instead of the Set ws..

  5. #5
    Registered User
    Join Date
    12-12-2012
    Location
    San Francisco, Californa
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation

    Since I am pretty new to vba, I'm unsure of what location to put the For Next. Or can I simply do a "Find" for the 1st sheet then select sheets to formatted after the find?
    Last edited by n_lindsey; 02-01-2013 at 05:31 AM.

  6. #6
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,613

    Re: Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation

    Uncomment the already included For-Next

    Comment the Set ws = Sheets("All_Fund_Series")

    That's it.

  7. #7
    Registered User
    Join Date
    12-12-2012
    Location
    San Francisco, Californa
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Clear Content of Select Cells in Newly Inserted Rows and Keep Data Validation

    Yes! Worked like a charm. Thank you.

+ 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