Cell value equal cell below

[Hatye]

Hi,

I've got a range in a sheet which contains value in every other row. I would like to set the value in row 1 equal value in row 2 in the columns E:I. So that E1=E2, F1=F2 and so on. Furthermore E3=E4, F3=F4 and so on.

It will differ from time to time how many rows which conatins data, so that part should be flexible.


I hope that you understand what I want to do, and that someone can help me with this

 

[MARK858]

Try on a copy of your data

Sub Rowbelow()
    Dim LstRw As Long, i As Long
    LstRw = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    For i = 1 To LstRw Step 2
        Range(Cells(i, 5), Cells(i, 9)).SpecialCells(4).Value = Range(Cells(i, 5), Cells(i, 9)).SpecialCells(4).Offset(1).Value
    Next
End Sub

or if you are certain it is only blank cells in the odd rows then just

Sub Rowbelow()
    Dim LstRw As Long, LstCo As Long, i As Long
    LstRw = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    For i = 1 To LstRw Step 2
        Range(Cells(i, 5), Cells(i, 9)).Value = Range(Cells(i, 5), Cells(i, 9)).Offset(1).Value
    Next
End Sub

 

[patel45]

Sub a()
LR = Cells(Rows.Count, "E").End(xlUp).Row
For j = 1 To LR Step 2
  Range("E" & j & ":I" & j).Value = Range("E" & j + 1 & ":I" & j + 1).Value
Next
End Sub

 

[JLGWhiz]

This assumes that the Range is confined to columns E:F.

Sub altRw()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("E1:F" & lr)
    For Each c In rng
        If c.Value = "" Then
            c.Value = c.Offset(1, 0).Value
        End If
    Next
End Sub

 

[Armando Montes]

Maybe F5:
Fill Blank Cells in Excel Column

 

[Rick Rothstein]

I've got a range in a sheet which contains value in every other row. I would like to set the value in row 1 equal value in row 2 in the columns E:I. So that E1=E2, F1=F2 and so on. Furthermore E3=E4, F3=F4 and so on.

The part I highlighted in red is not clear as to which cells have values and which are blank. I am assuming Row 1, and hence the odd numbered rows, have the values. With that said, give this non-looping macro a try...

Sub FillBlanksFromAbove()
  On Error GoTo NoBlanks
  Application.ScreenUpdating = False
  With Range("E1:I1").Resize(Cells(Rows.Count, "E").End(xlUp).Row + 1).SpecialCells(xlBlanks)
    .FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
NoBlanks:
  Application.ScreenUpdating = True
End Sub