Fill up the current cell with the value of the previous cell is the current cell is empty

[danex]

Hi,

I wrote the code below. It didn't given an error but wouldn't work either. My goal is to fill up the activecell with the value of the previous cell if the activecell is empty. For example if A2 is banana, A3 is apple and A4 is blank, then the value of A4 should be apple. This process will continue until the lastrow in variable lRow2. I was thining of using do-while-loop but i thought it would be simple to use for-loop.

Dim lRow2 As Double
    lRow2 = Columns(4).Cells.SpecialCells(xlCellTypeConstants).Count
    
MsgBox lRow2
    
For i = 3 To lRow2
    If Cells(lRow2, 1).Value = Empty Then Cells(lRow2, 1).Value = Cells(lRow2 - 1, 1).Value       
Next i

[AB33]

Most likely the cells are not empty but blank null string.

Sub FillDown()
Dim LR As Long

LR = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False

For i = 2 To LR
        If Cells(i, 4).Value = "" Then
            Cells(i, 4).Value = Cells(i - 1, 4).Value
    End If
Next i

Application.ScreenUpdating = True
End Sub

[soledad]

Select cell A2 (first cell in data range) then run macro as below

Sub emp()
Do
    If ActiveCell = "" Then
        ActiveCell = ActiveCell.Offset(-1)
        ActiveCell.Offset(1).Select
    Else
        ActiveCell.Offset(1).Select
    End If
Loop Until ActiveCell = "stop"
End Sub

[AB33]

lRow2 = Columns(4).Cells.SpecialCells(xlCellTypeConstants).Count

Counts the number of non empty cells which is not the same as you want to loop down until the last non empty cell in a column D.

lRow2 = Range("D" & Rows.Count).End(xlUp).Row

Should do

[danex]

i used a message box to know the value of lrow2 and it is just fine. I've revised my code (below) but still wouldnt work

Sub fillblanks()
Dim lr As Double
    lr = Range("D" & Rows.CountLarge).End(xlUp).Row - 1

For i = 2 To lr
    If IsEmpty(Cells(lr, 1).Value) Then Cells(lr, 1).Value = Cells(lr - 1, 1).Value
    Next i
End Sub

And here's my data:Attachment 281717

lRow2 = Columns(4).Cells.SpecialCells(xlCellTypeConstants).Count

Counts the number of non empty cells which is not the same as you want to loop down until the last non empty cell in a column D.

lRow2 = Range("D" & Rows.Count).End(xlUp).Row

Should do

[AB33]

lr = Range("D" & Rows.CountLarge).End(xlUp).Row - 1

looks at the last row minus in column D, while your other lines loop through column A

If IsEmpty(Cells(lr, 1).Value) Then Cells(lr, 1).Value = Cells(lr - 1, 1).Value

There is a problem with the attachment. I am unable to down load it.

[HaHoBe]

Hi, danex,

maybe try

On Error Resume Next
Range("A2:A" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
With Columns("B:B")
  .Value = .Value
End With
If Err <> 0 Then
  MsgBox "No blanks in Column A"
End If

Ciao,
Holger

[danex]

Thanks. But I figured out I should have used for-each-in a range. I had no success with do-while and for-1 to n.

Hi, danex,

maybe try

On Error Resume Next
Range("A2:A" & Cells(Rows.Count, "D").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
With Columns("B:B")
  .Value = .Value
End With
If Err <> 0 Then
  MsgBox "No blanks in Column A"
End If

Ciao,
Holger

[danex]

i used column only to find the last row with value - just the row number to bused in looping. The attachment is just an image.

lr = Range("D" & Rows.CountLarge).End(xlUp).Row - 1

looks at the last row minus in column D, while your other lines loop through column A

If IsEmpty(Cells(lr, 1).Value) Then Cells(lr, 1).Value = Cells(lr - 1, 1).Value

There is a problem with the attachment. I am unable to down load it.

[AB33]

The attachment is just an image.
Can not even see the image.
Please refer to my post #2.
If you had attached a sample, It would have been resolved two days. We now are on the 3rd day.

[danex]

Here's the image data.jpg

[AB33]

A Picture is not much help. You can not test a picture.
To test if column A cells are blank, with out a code:
CRTL plus G will take you to "go special" then click blank and see if any find any cells highlighted.

[danex]

Here is the spreadsheet via google doc https://drive.google.com/file/d/0B1k...it?usp=sharing