Make blank cell actually blank....

test3xc31

New Member
Joined
Jun 11, 2019
Messages
27
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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






 
Upvote 0
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)
 
Upvote 0
What size is the range "Lalllo"?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
If the range is worksheet cope then you will need to add the sheet name to the front of that line.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top