Make blank cell actually blank....

test3xc31

New Member
Joined
Jun 11, 2019
Messages
24
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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,680
Office Version
365
Platform
Windows
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





 

test3xc31

New Member
Joined
Jun 11, 2019
Messages
24
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,993
Office Version
365
Platform
Windows
What size is the range "Lalllo"?
 

test3xc31

New Member
Joined
Jun 11, 2019
Messages
24
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,993
Office Version
365
Platform
Windows
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
 

test3xc31

New Member
Joined
Jun 11, 2019
Messages
24
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
 

test3xc31

New Member
Joined
Jun 11, 2019
Messages
24
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:
[COLOR=#333333]Range("ADalllo").SpecialCells(xlCellTypeBlanks).Offset(, -2).Value = ""[/COLOR]
in Clearvar
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,993
Office Version
365
Platform
Windows
If the range is worksheet cope then you will need to add the sheet name to the front of that line.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,192
Messages
5,448,884
Members
405,537
Latest member
muh6323

This Week's Hot Topics

Top