Filling in blank cells with values from another workbook

Occasional Visitor

Hello all, I'm looking for help writing a macro which does the following but struggling with a few areas, I'd like to do the following: 


1) Identify all blank cells in a given range. All blank cells are completely randomly placed, not full columns, rows or blocks so Excel won't recognise the set of cells as a range. The workbook here is called "NIR". I've got this "if" statement set up already because that was nice and easy, nothing in it yet.


If IsEmpty(Range("C8:MX")) = True Then

End If



2) Fill the blank values from the NIR workbook with values from a second workbook called "VIS" but this can only effect the blank cells in the NIR workbook NOT the filled cells. The values I want to paste in are in the same cells respectively; so if I have a blank cell A12 in NIR I would fill it in with the value in cell A12 in VIS. 


Thanks in advance!


4 Replies

@Veraborn64 You could use a macro similar to this:

Sub FillTheGaps()
    Dim sourceCell As Range
    Dim NIR As Workbook
    Dim VIS As Workbook
    Set NIR = ThisWorkbook 'Replace this with something like Workbooks("NIR.xlsx")
    Set VIS = ThisWorkbook 'Replace this with something like Workbooks("VIS.xlsx")
    'Assuming the name of the worksheet is the same
    For Each sourceCell In NIR.Worksheets("Sheet1").Range("C8:M100").SpecialCells(xlCellTypeBlanks)
        sourceCell.Value = VIS.Worksheets("Sheet1").Range(sourceCell.Address).Value

End Sub


It would be helpful for all of us if you could add a file (without sensitive data). This way you can hope for an answer faster and we can offer you an answer faster, as far as possible.

Unfortunately, I can't do much with the term "empty values from the NIR workbook".

I know I don't know anything (Socrates)
Hallo Mr. Jan Karel Pieterse, I saw your contribution too late, after the translation was not so clear to me it was requested ... but if it is, thank you from me too


This outlines an approach that load values to an from the workbook(s) as a block and sorts the data transfer between arrays held in memory.

Option Explicit
Option Base 1

Sub transfer()
Dim vSource
Dim vDestination
Dim i As Long, j As Long
Dim v
'   Load Source and Destination ranges into variant arrays
    vSource = [Source].Value
    vDestination = [Destination].Value
'   Loop over elements of array copying values to empty locations
    For i = 1 To UBound(vDestination, 1)
        For j = 1 To UBound(vDestination, 2)
            v = vDestination(i, j)
            If v = vbEmpty Then _
                vDestination(i, j) = vSource(i, j)
        Next j
    Next i
 '  Return augmented array to destination range
    [Destination].Value = vDestination
End Sub