Add data in blank cells in a column

[NewToVBA1]

I have a series of blank and non-blank data in a column. The number of blank data are fixed (for example 50) and the number of non-blank data is one. This repeats a number of times. I need to fill the blank cells with the non-blank data above it. I have used the following code. The code works fine until cell above the last non-blank data. However, it does not enter the data after the last 50 data after the last non-blank data, because it cannot find a non-blank data below. I can manualy enter a dummy data at the last 51st cell and then it works fine, but then it is not automatic.
Can anybody help?

The code is:

Sub FillEmpty()
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim cell As Range
Dim rRange1 As Range
Set ws = ThisWorkbook.Sheets("Dummy2") 'Dummy2 is the name of the worksheet where the data is.

For Each cell In ws.Range("A3", ws.Range("A" & Rows.Count).End(xlUp))
If Trim(cell) = "" And cell.Row > 1 Then
cell.NumberFormat = cell.Offset(-1, 0).NumberFormat
cell.Value = (cell.Offset(-1, 0).Value)
End If
Next cell
Application.Calculation = xlAutomatic 'xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub

 

[Jonmo1]

Welcome to the board...

For Each cell In ws.Range("A3", ws.Range("A" & Rows.Count).End(xlUp))

That is using column A to determine the last non blank row..
And that is not accurate...
Try using an adjescent column (that is full) to determine the last used row

Try

Dim LR As Long
LR = ws.Cells(Rows.Count, "B").End(xlup).Row 'Use a column that is full
For Each cell In ws.Range("A3:A" & LR)

Hope that helps.

 

[NewToVBA1]

Thanks. But I don't have any other data. i am getting this data from a different software as output.
I am sure there will be a way to add 50 data after the last available non-blank data

 

[Jonmo1]

You could do

For Each cell In ws.Range("A3", ws.Range("A" & Rows.Count).End(xlUp).Offset(50,0))

 

[JackDanIce]

It is due to the type of defined loop you are using, a FOR LOOP. Instead, this may be a better approach in your situation:

Sub FillEmpty()

Dim ws As Worksheet
Dim i As Long, j As Long
Dim msg As String

With Application
    .ScreenUpdating = False
    .Calculation = xlManual
End With

j = 50
' change 50 to the number of blank rows
i = 2
'change 2 to whatever row contains your first data you THEN want copied down

Do While Not IsEmpty(Range("A" & i)) Or Len(Range("A" & i)) > 0
    Range("A" & i).AutoFill Range("A" & i & ":A" & i + j)
    i = i + j + 1
Loop

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

 

[NewToVBA1]

Thank you very much it works fine now. I really appreciate your time.

 

[NewToVBA1]

Thanks to both of you:

Jonmo1 and JackDanIce

 

[JackDanIce]

You're welcome

 

[Jonmo1]

Glad to help, thanks for the feedback

 

[JackDanIce]

It is nice when there's acknowledgement isn't it Jonmo!