CODEWITHSUNDEEP
vbaexcelcells
Liviu-Adrian
Liviu-Adrian

Reputation: 57

Excel VBA: Fill in empty cells with adjacent cell values

I was wondering if someone can help -

I have a column 'I' containing cells of different values; some of these cells are blank. I also have an adjacent complete column 'G' - no blank cells.

What I'm basically trying to do is use VBA to fill in the empty cells in column 'I' with the value of their adjacent cells in column 'G'.

I wrote the below code in a command button: 

Sub CommandButton2_Click()

   Lastrow = Cells(Rows.Count, "G").End(xlUp).Row

   Range("I2:I" & Lastrow).SpecialCells(xlCellTypeBlanks).Value = Range("G2:G" & Lastrow).Value

End Sub

The above code populates the blank 'I' cells, but it always seems to start with the second 'G' cell when populating. I want it to populate the blank 'I' cell with it's adjacent 'G' cell, and not start counting from the beginning when it finds a blank cell.

Hope someone can help. Let me know if I need to clarify things further.

Regards,

Adrian

Upvotes: 2

Views: 1904

Answers (1)

SierraOscar
SierraOscar

Reputation: 17627

Dim blanks As Excel.Range

Set blanks = Range("I2:I" & Cells(Rows.Count, 7).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)

blanks.Value = blanks.Offset(0, -2).Value

Upvotes: 3

Related Questions