Copy range if cells are blank

[skinnyboy]

I have range D2:D40 with data that gets updated chronologically from left to right. (D2 on Monday, D3 Tuesday, D4 Friday, etc). If we haven't reached the time of the update, then the cell is going to be blank. I also have H2:H40 that all has data. I want to create a macro that checks row D to see if the cells are blank, and if they are, replace them with the information in the corresponding column from row H (copy H10 to D10, H11 to D11, etc). Is there a simple way to do this? Does it help to know that if D10 is blank, then D11:D40 are also going to be blank?

 

[footoo]

With [D2:D40].SpecialCells(xlCellTypeBlanks)
    .Value = .Offset(0, 4).Value
End With

 

[skinnyboy]

Oops, sorry! I typed my original question from memory and got it wrong as to how the data was laid out.

The range that gets manually updated is all in row 4. It's C4, E4, G4... CC4. (C4 & D4 are merged into 1 cell, as are E4 & F4, etc). I want to copy the data from row 10 - so that if M4 is blank, it's set to the value of M10.

Can this still be done?

 

[footoo]

Does row 10 also consist of merged cells?

 

[skinnyboy]

Does row 10 also consist of merged cells?

Yes.

 

[footoo]

Dim r As Range, c%
Set r = [D4:CC4]
For c = 1 To r.Cells.Count Step 2
    If r(c) = "" Then r(c).Value = r(7, c).Value
Next

 

[skinnyboy]

Dim r As Range, c%
Set r = [D4:CC4]
For c = 1 To r.Cells.Count Step 2
    If r(c) = "" Then r(c).Value = r(7, c).Value
Next

And if I wanted to do the same thing for row 11, I just change the 7 to an 8?

 

[footoo]

And if I wanted to do the same thing for row 11, I just change the 7 to an 8?

Yes.