CODEWITHSUNDEEP
vbaexcel
Juan Sebastian Medina
Juan Sebastian Medina

Reputation: 43

Ignore blank cells in a range

Beginner in VBA here. I'm trying to pull a range of cells into a different workbook and when it does, it pastes blanks as "12:00:00AM." I'm trying to have it copy over exactly, having blanks in the target workbook just as in the source.

Current code

Sub GetDinServRange()

With Range("A37:E111")
    If Not IsEmpty("A37:E111") Then
        With Range("A37:E111")
            .Formula = "='C:\FILEPATH\FILE'!A37:E111"
            .Value = .Value
        End With
    End If
End With

End Sub

Upvotes: 0

Views: 403

Answers (1)

Trashman
Trashman

Reputation: 1596

Change

.Formula = "='C:\FILEPATH\FILE'!A37:E111"

to

.Formula = "=IF(ISBLANK('C:\FILEPATH\FILE'!A37:E111),"""",'C:\FILEPATH\FILE'!A37:E111)"

or

.Formula = "=IF(ISBLANK('C:\FILEPATH\FILE'!A37:E111)," & CHR(34) & CHR(34) & ",'C:\FILEPATH\FILE'!A37:E111)"

Note: Either Double Double Quotes ("""") or CHR(34) are needed in VBA to make Double quotes appear in the actual Excel formula.

Upvotes: 1

Related Questions