Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

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

    Exclamation Make blank cell actually blank....

    I can't figure this out. The code all works as required however... when referance "Lalllo" is pasted into range "I32" the blanks it pastes are not actual blanks in terms of what sub Clearvar is doing I get error message 1004 no cells were found. If I then manually click on each blank cell and press 'Delete' then run the script, perfect, no problem. This does however kind of undermine what I was trying to achieve. Any help would be seriously appreacted.

    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
        Application.Goto Reference:="Lalllo"
        Selection.copy
        Sheets("AnnualData").Select
        Range("I32").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=True, Transpose:=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).Value = ""
        
    End Sub

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,400
    Post Thanks / Like
    Mentioned
    60 Post(s)
    Tagged
    7 Thread(s)

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

    To help you we need to know exactly what the pseudo-blank cells actually contain

    1. Do they contain
    - spaces
    - a formula which equates to ""
    - an invisible apostrophe '
    - something else?

    2. Do you want an empty string in the cells
    Code:
    Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).Value = ""
    How about ..
    Code:
    Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).ClearContents






  3. #3
    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....

    Just a further point, the cells that are being copied from ref "Lalllo" return ISBLANK false, even the empty ones, This may be where the problem is. Is there a way to get around this, my current formula in these cells is =IF(E23="","",E23). What I really want is =IF(E23,"",RETURN AN ACTUAL BONIFIED BLANK CELL PLEASE,E23)

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

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

    What size is the range "Lalllo"?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    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....

    Thank you for replying, unfortunatly not though. The problem occours earlier in the form that the displayed blank cells are actually "", so not truely blank

  6. #6
    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
    what size is the range "lalllo"?
    i32:i85

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

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

    Ok, how about
    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
        
       With Sheets("AnnualData")
          .Unprotect
          .Range("I32:I85").Value = Sheets("Loads").Range("Lalllo")
          Call Clearvar
          .Range("ADchangingdata").ClearContents
    
          .Range("Havestyear").Value = Range("Harvestyear").Value + 1
          .Protect
       End With
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    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
    Ok, how about
    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
        
       With Sheets("AnnualData")
          .Unprotect
          .Range("I32:I85").Value = Sheets("Loads").Range("Lalllo")
          Call Clearvar
          .Range("ADchangingdata").ClearContents
    
          .Range("Havestyear").Value = Range("Harvestyear").Value + 1
          .Protect
       End With
    End Sub

    I see what you've done there, much tidier than my work! I'm getting a different error with that though 424 Object required

  9. #9
    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 test3xc31 View Post
    I see what you've done there, much tidier than my work! I'm getting a different error with that though 424 Object required
    Should have mentioned the error is when it calls this line
    Code:
    Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).Value = ""
    in Clearvar

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

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

    If the range is worksheet cope then you will need to add the sheet name to the front of that line.
    - 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
  •