Make blank cell actually blank....

[test3xc31]

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.

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

 

[Yongle]

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

Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).Value = ""

How about ..

Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).ClearContents

 

[test3xc31]

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)

 

[Fluff]

What size is the range "Lalllo"?

 

[test3xc31]

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

 

[test3xc31]

what size is the range "lalllo"?

i32:i85

 

[Fluff]

Ok, how about

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

 

[test3xc31]

Ok, how about

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

 

[test3xc31]

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

[COLOR=#333333]Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).Value = ""[/COLOR]

in Clearvar

 

[Fluff]

If the range is worksheet cope then you will need to add the sheet name to the front of that line.