Page 2 of 2 FirstFirst 12
Results 11 to 19 of 19

Thread: Make blank cell actually blank....
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    New Member
    Join Date
    Jun 2019
    Location
    UK
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make blank cell actually blank....

    Quote Originally Posted by Fluff View Post
    If the range is worksheet cope then you will need to add the sheet name to the front of that line.
    Im sorry I'm not sure I follow do you mean

    Code:
    Sub Clearvar()    'On Error Resume Next
            Sheets("AnnualData").Select
            Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).Value.ClearContents
        
    End Sub
    If so unfortunatly still a 424 object required

  2. #12
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Make blank cell actually blank....

    Are any cells in range I32:I85 protected or merged?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #13
    New Member
    Join Date
    Jun 2019
    Location
    UK
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make blank cell actually blank....

    Quote Originally Posted by Joe4 View Post
    Are any cells in range I32:I85 protected or merged?
    None are merged, some are protected however these are all unprotected in the script ie
    Code:
    ctiveSheet.Unprotect
    . Ive also tried with the setting the cells to unlocked as a test with the same result

  4. #14
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Make blank cell actually blank....

    None are merged, some are protected however these are all unprotected in the script
    That only works if they are protected WITHOUT a password.
    If that range is password protected, that code won't work.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #15
    New Member
    Join Date
    Jun 2019
    Location
    UK
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make blank cell actually blank....

    Quote Originally Posted by Joe4 View Post
    That only works if they are protected WITHOUT a password.
    If that range is password protected, that code won't work.
    yup no password

  6. #16
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,923
    Post Thanks / Like
    Mentioned
    406 Post(s)
    Tagged
    42 Thread(s)

    Default Re: Make blank cell actually blank....

    Do you have a named range called Adalllo?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #17
    New Member
    Join Date
    Jun 2019
    Location
    UK
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make blank cell actually blank....

    Quote Originally Posted by Fluff View Post
    Do you have a named range called Adalllo?
    Yes ADalllo ie AnnualData (the sheetname) all leftover

  8. #18
    New Member
    Join Date
    Jun 2019
    Location
    UK
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Make blank cell actually blank....

    Got it, its not pretty and it need refining but it now works

    Code:
    Sub leftover()
    
    response = MsgBox("Are you sure you want to end this year!? this cannot be undone", vbYesNo)
     
    If response = vbNo Then
     MsgBox ("Operation Cancelled")
        Exit Sub
    End If
        
        Sheets("AnnualData").Select
        ActiveSheet.Unprotect
        Sheets("Loads").Select
        ActiveSheet.Unprotect
        Range("Lalllo").Select
        Selection.copy
        Sheets("AnnualData").Select
        Range("I32").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        Selection.Replace What:="#", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=True, _
            ReplaceFormat:=False
        Call Clearvar
        Application.Goto Reference:="ADchangingdata"
        Selection.ClearContents
        Range("Havestyear").Value = Range("Harvestyear").Value + 1
        ActiveSheet.Protect
        Range("D9").Select
       
       End Sub
    
    
    
    
    
    
    Sub Clearvar()
        On Error Resume Next
            Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).ClearContents
        
    End Sub
    If you were wondering the reason Clearvar wasn't working was because of a sneeky extra .Value

    Thank you all for your help!

  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    23,923
    Post Thanks / Like
    Mentioned
    406 Post(s)
    Tagged
    42 Thread(s)

    Default Re: Make blank cell actually blank....

    Glad you sorted it & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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