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

 

[test3xc31]

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

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

 

[Joe4]

Are any cells in range I32:I85 protected or merged?

 

[test3xc31]

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

ctiveSheet.Unprotect

. Ive also tried with the setting the cells to unlocked as a test with the same result

 

[Joe4]

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.

 

[test3xc31]

That only works if they are protected WITHOUT a password.
If that range is password protected, that code won't work.

yup no password

 

[Fluff]

Do you have a named range called Adalllo?

 

[test3xc31]

Do you have a named range called Adalllo?

Yes ADalllo ie AnnualData (the sheetname) all leftover

 

[test3xc31]

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

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!

 

[Fluff]

Glad you sorted it & thanks for the feedback