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]

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"

That indicates that there are no blank cells in the range being considered.
.. which is why I said right back at the beginning ..

Assuming ..
- the blank cells are truly blank

Are you convinced there are blank cells in the range being considered?
Just one possibility: The cells that look blank actually contain formulas returning ""?

The following question still stands:

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

 

[danbates]

Hi Peter,

Right ok. I now see where I have gone wrong in my original question.

There are 2 ways the worksheet can be entered.

Sometimes there will be one entry meaning no blanks cells and sometimes there will be multiple entries and that's when there will be blank cells in columns G:I but starting from row 3.

I hope this clears things up a little and I am sorry for the inconvenience.

Thanks

Dan

 

[Peter_SSs]

Sometimes there will be .. no blanks cells and sometimes there will be .. blank cells in columns G:I but starting from row 3.

Try

Sub FillBlanks_v2()
  Dim Rng As Range, RngBlanks As Range
  
  Set Rng = Range("G3", Range("I" & Rows.Count).End(xlUp))
  On Error Resume Next
  Set RngBlanks = Rng.SpecialCells(xlBlanks)
  On Error GoTo 0
  If Not RngBlanks Is Nothing Then
    RngBlanks.FormulaR1C1 = "=R[1]C"
    Rng.Value = Rng.Value
  End If
End Sub

 

[danbates]

Hi Peter,

I have run your code but it still triggers the Run-time error '1004': No cells were found. when entering a single entry.

It highlights this line:

Set RngBlanks = Rng.SpecialCells(xlBlanks)

Thanks again

Dan

 

[Peter_SSs]

.. when entering a single entry.

Can you explain exactly what that means?

Have you included the blue line in the code?

On Error Resume Next
Set RngBlanks = Rng.SpecialCells(xlBlanks)

 

[danbates]

Hi Peter,

I did copy and paste your code before but I have done it again tonight and everything seems to be ok, so thank you for all your help.

It really is appreciated.

Thanks again

Dan