Is there a faster way to replace blank rows with previous

[GeeWhiz7]

Hi guys,

I have ~180 rows of data (~2500 cells) that normally are fine.
Occasionally when I get the data (date/price from yahoo) using another macro, a row may be blank or contain null which throws off later working macros.

I have a working macro to get rid of that issue, but it takes a LONG time to run since it is going cell/row by row.

Is there a faster way? I have tried to put the data into a temporary array, but struggle once it is there to find out how to replace a blank/null row with previous so I go back to the Long way.

Sub FillCellFromAbove()

Application.ScreenUpdating = False

Dim answer As Integer

answer = MsgBox("ONLY FIX NULLS OR BLANKS IF THE PROBLEMS CELL SHOWS A VALUE", vbInformation + vbOKCancel, "Fix Nulls or Not")

Select Case answer
    Case vbOK
    
        Sheets("Adjusted Close Price").Select
        Range("B2:AY181").Select

        For Each Row In Selection
        If Row.Value = "" Or Row.Value = "null" Then
        Row.Value = Row.Offset(-1, 0).Value
        End If
        Next Row

    
    Case vbCancel
End Select

Application.ScreenUpdating = True

End Sub

 

[DanteAmor]

Try this:

Sub FillCellFromAbove()
  On Error Resume Next
  With Sheets("Adjusted Close Price").Range("B2:AY181")
    .Replace "null", "", xlWhole, xlByRows, False
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

 

[GeeWhiz7]

Try this:

Sub FillCellFromAbove()
  On Error Resume Next
  With Sheets("Adjusted Close Price").Range("B2:AY181")
    .Replace "null", "", xlWhole, xlByRows, False
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub

Thank you Dante! Well, it's more than 1000 times faster so that is impressive. So if I understand correctly, the .Replace is handling anything that is "null" and the .SpecialCells(xlCellTypeBlanks) is handling anything that is blank by assigning the Row - 1 offset as the value.

I'm not sure I understand what .Value=.Value is doing though.

 

[DanteAmor]

So if I understand correctly, the .Replace is handling anything that is "null"

Replaces it with empty.

and the .SpecialCells(xlCellTypeBlanks) is handling anything that is blank by assigning the

By the formula =(cell above)

.Value=.Value is

Since all the replaced ones stay with the formula, now it changes the formulas for the same value of the cell.

 

[GeeWhiz7]

Replaces it with empty.


By the formula =(cell above)


Since all the replaced ones stay with the formula, now it changes the formulas for the same value of the cell.

Got it, thanks again for the solution and also for the explanation!