I need a code altering that auto fills from the bottom up.

[danbates]

Hi,

Please can someone help me?

I have the following code that fills any blank cells above the last cell with a value and it fills the blank cells above with that value.

At the moment it will fill column G from the bottom up to cell G7.

Example:
If cell G500 has "X" and cell G250 has "Y" in it and I execute the code, the cells from G500:G251 will have a"X" and cells G250:G7 will have a "Y".

I would like the code to still fill the same but I would like it to stop once it sees another value.

Example:
If cell G500 has "X" and cell G250 has "Y" in it and I execute the code, the cells from G500:G251 will have a"X" and then stops.

Here is my code:

Sub FillBlanks()

  Dim Rng As Range, RngBlanks As Range

  Set Rng = Range("G7", Range("G" & 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

Any help would be appreciated.

Regards

Dan

 

[Rick Rothstein]

Give this macro a try...

Sub FillLastBlanksOnly()
  Dim BlnkRng As Range, ConstRng As Range
  Set BlnkRng = Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlBlanks)
  Set ConstRng = Range("A1", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlConstants)
  BlnkRng.Areas(BlnkRng.Areas.Count).Value = ConstRng.Areas(ConstRng.Areas.Count)(1).Value
End Sub

 

[danbates]

Hi Rick,

Your code works perfectly thank you but I've had a thought.

Is it possible to have the code looking from A500:A1 instead of looking at the whole of column A?

Thanks again

Dan

 

[Rick Rothstein]

My code does not look at the whole of Column A... it looks from the last cell with data upward. Did you misunderstand that or are you saying you have data and blanks in cells past cell A500 that you want to ignore? If the latter, then you have to tell us what to do when, say, cells A491:A500 are blank, A490 is not blank and cells A480:A489 are blank... what is the last blanks to be filled, cells A480:A489 or cells A491:A500?

 

[danbates]

Hi Rick,
I did misunderstand your code and yes I have a 'helper' row (Row 1000) that I would like your code to ignore.
My data finishes at row 500. There will be no data in any columns from row 501:999.

Thanks again for your help.

Dan

 

[Rick Rothstein]

Given what you posted, this should work for you...

Sub FillLastBlanksOnly()
  Dim BlnkRng As Range, ConstRng As Range
  Set BlnkRng = Range("A1", Cells(501, "A").End(xlUp)).SpecialCells(xlBlanks)
  Set ConstRng = Range("A1", Cells(501, "A").End(xlUp)).SpecialCells(xlConstants)
  BlnkRng.Areas(BlnkRng.Areas.Count).Value = ConstRng.Areas(ConstRng.Areas.Count)(1).Value
End Sub

 

[danbates]

Hi Rick,

Absolutely perfect thank you. I really appreciated it.

Regards

Dan