VBA to fill the empty cells above from the value below.

[danbates]

Hi,

Please could someone help me out?

I am trying to get a VBA code that fills all the empty cells above in column G with the same value that is below.

So lets say 'DB' is in cell G20 and 'AL' is in cell G10.

I would like the code to fill cells G19:G11 with 'DB'

Any help would be much appreciated.

Thanks

Dan

 

[Peter_SSs]

Assuming ..
- the blank cells are truly blank
- column G does not contain any formulas that need to be retained
.. try

Sub FillBlanks()
  With Range("G1", Range("G" & Rows.Count).End(xlUp))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[1]C"
    .Value = .Value
  End With
End Sub

 

[danbates]

Hi Peter,

Your code works perfectly, Thank you.

Is it possible to widen your code to do exactly the same in column H and I?

Thanks again

Dan

 

[Peter_SSs]

Is it possible to widen your code to do exactly the same in column H and I?

It is, but the best method of doing so depends on the possible structure of your data, which we don't know about.
Does this do what you want?
If not, tell us more about those 3 columns. In particular, whether they all finish on the same row or not.

With Range("G1", Range("I" & Rows.Count).End(xlUp))

 

[danbates]

Hi Peter,

Your code does exactly what I was wanting. Thank you again for all your help.

Kind Regards

Dan

 

[Peter_SSs]

Good news. You're welcome.

 

[danbates]

Hi Peter,

This code you did for me yesterday is now coming up with an 'Empty Cell Error'.

Sub FillBlanks()With Range("G3", Range("I" & Rows.Count).End(xlUp))
.SpecialCells(xlBlanks).FormulaR1C1 = "=R[1]C"
    .Value = .Value
  End With
End Sub

I've altered the header and that's why I am getting the error.

The reason it was ok before was because I had merged the header meaning cell G1 was empty.

I need it to run from G3 now and as you can see I've change the code from G1 to G3 hoping that would sort it but it hasn't.

Is there an easy fix?

Thanks again

Dan

 

[MARK858]

Exactly what cells were merged?

 

[Peter_SSs]

Can you give us a small set of dummy data for which the code fails?

If there are still any merged cells involved in the sample please make clear which one(s).

What is the exact error message?

 

[danbates]

Hi,

The error is: Run-time error '1004': No cells were found.

and when I debug, it highlights this line

.SpecialCells(xlBlanks).FormulaR1C1 = "=R[1]C"

The merged cells was A1:J1 and that had the sheet header in.

Now there isn't any merged cells and the data I will be entering onto starts on row 3.

Thanks again

Dan