filled up a column with data on blank cell

Question

How to use VBA to copy data from i.e sheet2, fill a range (1 column) on blank cells at sheet1

i.e at sheet1 ( -blankcell- = blank)

-blankcell-  
-blankcell-  
-blankcell-
ahjefe
-blankcell-
-blankcell-
23234
***fg4
-blankcell-
8569
-blankcell-

at sheet2 (data to be used on sheet1 blank cell)

aaa
bbb
ccc

END result at sheet1

aaa
bbb
ccc
ahjefe
aaa
bbb
23234
ccc
aaa
8569
bbb <- stopped at before last blank row

+1 on @PowerUser's comment. If you show us what you've tried, we can help you get past your stuck points. — Scott Holtzman

Scott Holtzman Scott Holtzman · Accepted Answer · 2012-10-23T15:49:07

I have some free time, so I wrote this code for you below. However, it's best to always show your efforts when positing questions. Usually people don't just write code for you.

Sub FillBlanks()

Dim wks1 As Worksheet, wks2 As Worksheet

Set wks1 = Sheets(1) 'change to your needs; could also be Sheets("Sheet1") format
Set wks2 = Sheets(2) 'change to your needs; could also be Sheets("Sheet1") format

Dim rngLookup As Range

'assume data on sheet2 is on column A
With wk2
    Set rngLookup = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
End With

With wks1

    Dim rngSearch As Range
    'assume blank cells are in column A on sheet 1
    Set rngSearch = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp))
    Set rngSearch = rngSearch.SpecialCells(xlCellTypeBlanks)

    Dim cel As Range, i As Integer

    i = 1

    For Each cel In rngSearch

        cel.Value = rngLookup.Cells(i, 1)
        If i = rngLookup.Rows.Count Then i = 1 Else: i = i + 1

    Next


End With

End Sub

filled up a column with data on blank cell

1 Answers