CODEWITHSUNDEEP
vbaexcel
CodeCamper
CodeCamper

Reputation: 6980

Excel adding an ' to the beginning of all my cells (APOSTROPHE being magically added)

With ThisWorkbook.Sheets("WorksheetName").Cells.Range("A1:A1000000")
.Formula = formulavariable
.Value = .Value
End With

When Excel does .Value=.Value it adds an ' to the beginning of all the values. If I right click and paste as value it removes the '. The problem is this code is extremely efficient and takes less than 1 second when I try to remove the ' it takes forever to load all the cells.

Upvotes: 0

Views: 5541

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Give this a try:

Sub testit()
    formulavariable = "=1+2"
    With ThisWorkbook.Sheets("WorksheetName").Cells.Range("A1:A1000000")
        .Clear
        .Formula = formulavariable
        .Value = .Value
    End With
End Sub

EDIT

If you wish to fill the cells with a Null then:

Sub FillWithNulls()
    formulavariable = Chr(61) & Chr(34) & Chr(34)
    With ThisWorkbook.Sheets("WorksheetName").Cells.Range("A1:A1000000")
        .Clear
        .Formula = formulavariable
        .Value = .Value
    End With
End Sub

Upvotes: 1

Related Questions