+ Reply to Thread
Results 1 to 7 of 7

Code to clear contents of a cell based on specific criteria

  1. #1
    Registered User
    Join Date
    07-03-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    19

    Code to clear contents of a cell based on specific criteria

    I have a report with the existing code. My report has many formulas making it very large. I use this code to export my data into a seperate workbook so I can forward the report to recipients. This code works perfectly for me and for sending to others. However, the export I send out does not work perfectly for others. When I send the report to the recipients, the recipients like to use it for vlookups in their reports to gather information from my report. When they add vlookups to their reports to pull information out of this report they get #Values for the blank cells from my report. When they clear the contents on the blank cells of my report their vlookups work fine. I am assuming when I use this code to export to an excel spreadsheet it is putting invisible characters in the blank cells.

    Is there a way to add to the formula below that will clear the contents of the blank cells?


    Sub TwoSheetsAndYourOut()
    Dim NewName As String
    Dim nm As Name
    Dim ws As Worksheet

    If MsgBox("Copy specific sheets to a new workbook" & vbCr & _
    "New sheets will be pasted as values, named ranges removed" _
    , vbYesNo, "NewCopy") = vbNo Then Exit Sub

    With Application
    .ScreenUpdating = False

    On Error Resume Next
    Sheets(Array("Summary", "Report", "Raw Data")).Copy
    On Error Resume Next

    For Each ws In ActiveWorkbook.Worksheets
    ws.Cells.Copy
    ws.[A1].PasteSpecial Paste:=xlValues
    ws.Cells.Hyperlinks.Delete
    Application.CutCopyMode = False
    Cells(1, 1).Select
    ws.Activate
    Next ws
    Cells(1, 1).Select

    For Each nm In ActiveWorkbook.Names
    nm.Delete
    Next nm

    NewName = InputBox("Please Specify the name of your new workbook", "New Copy", "Report ")

    ActiveWorkbook.SaveCopyAs ThisWorkbook.Path & "\" & NewName & ".xlsx"
    ActiveWorkbook.Close SaveChanges:=False

    .ScreenUpdating = True
    End With
    Exit Sub

    ErrCatcher:
    MsgBox "Specified sheets do not exist within this workbook"
    End Sub

    Thank you for your time.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Code to clear contents of a cell based on specific criteria

    Please use code tags with your code.

    Please Login or Register  to view this content.
    INTO

    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Code to clear contents of a cell based on specific criteria

    "When they add vlookups to their reports to pull information out of this report they get #Values for the blank cells from my report"

    Ask the user to include an iferror function on their vlookup formula so that if there is no match, the return value would be 0
    Last edited by AB33; 11-21-2013 at 12:15 PM.

  4. #4
    Registered User
    Join Date
    07-03-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Code to clear contents of a cell based on specific criteria

    Re: Code to clear contents of a cell based on specific criteria
    Please use code tags with your code.


    ws.Cells.Copy

    INTO

    ws.usedrange.Copy


    I received feedback from one person stating this change didn't make a difference but I am keeping this option in going forward. I am starting to think it is the individual not the report or the code. Thank you so much for taking the time to review and answer my post.

    It is greatly appreciated.

  5. #5
    Registered User
    Join Date
    07-03-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Code to clear contents of a cell based on specific criteria

    Quote Originally Posted by AB33 View Post
    "When they add vlookups to their reports to pull information out of this report they get #Values for the blank cells from my report"

    Ask the user to include an iferror function on their vlookup formula so that if there is no match, the return value would be 0
    There are 31 people this is sent out to and they all use it in different ways. I thought it would be easier to provide a product to all to use without issues rather than assisting to help each one.

  6. #6
    Registered User
    Join Date
    07-03-2012
    Location
    Chicago, IL
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Code to clear contents of a cell based on specific criteria

    [QUOTE=srsev;3484308]Re: Code to clear contents of a cell based on specific criteria
    Please use code tags with your code.


    ws.Cells.Copy

    INTO

    ws.usedrange.Copy


    I received feedback from one person stating this change didn't make a difference but I am keeping this option in going forward. I am starting to think it is the individual not the report or the code. Thank you so much for taking the time to review and answer my post.

    It is greatly appreciated.

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,122

    Re: Code to clear contents of a cell based on specific criteria

    Are not you the one who sends the report? If so, you know what the report looks like. When you run the report, do you get blank cells when pasted?

    You can also add another to delete all rows which are blank, but I do not know what causes these blank cells to be in the first place.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Code to clear contents of specific cells based upon if key exists in other sheets
    By seputus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2013, 07:25 PM
  2. Clear Cell Contents Based on Criteria
    By AdamsR in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2012, 01:52 PM
  3. [SOLVED] Macro to clear contents based on specific criteria
    By greggatz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2012, 10:26 AM
  4. [SOLVED] How to clear cell contents in a certain column based on set criteria
    By macrohelp5 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2012, 10:41 AM
  5. [SOLVED] Clear Cell Contents Based On Criteria
    By xybadog in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-03-2012, 11:47 AM

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