setting cell to equal cell below

Todd77

New Member
Joined
Aug 20, 2019
Messages
12
Hi all,

I have blank rows in a table that I want to have equal the row below. The idea is that, while cells immediately below might also be blank, we will eventually hit a row that isn't blank and the formerly blank cells would take those values.

I used the macro recorder to get a sense of how to write the code. However, it only gives specific cell references. Can anyone help?

Todd77
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Hi there. This code looks upwards in column A and if any blank is found, the row below (either just column A or the whole row depending on which line you leave in of my 2 commented lines) is copied into the row above. I wasn't quite sure if you want the cell or the row copied.

Code:
Sub fillup()
Dim FromRow As Long
Dim ThisRow As Long
' column A assumed to look for blanks
With ActiveSheet
    FromRow = .Range("A65536").End(xlUp).Row
' works from the last row with data in column A to row 1 (stepping backwards)
    For ThisRow = FromRow To 1 Step -1
        If .Range("A" & ThisRow).Value = "" Then
        
            Range("A" & ThisRow).Value = Range("A" & ThisRow + 1).Value  ' use this line if you only want column A value

            .Rows(ThisRow).EntireRow.Value = .Rows(ThisRow + 1).EntireRow.Value   ' use this line if you want the whole row
            
        End If
    Next ThisRow
End With
End Sub
 
Last edited:
Upvote 0
Hi
Or may be
Code:
Sub fill_empty()
    lr = ActiveSheet.UsedRange.Rows.Count
    lc = ActiveSheet.UsedRange.Columns.Count
    x = Range("a1:a" & lr).SpecialCells(xlCellTypeBlanks).Address
    x = Split(x, ",")
    For i = 0 To UBound(x)
        Range(x(i)).Offset(-1).Resize(, lc).Copy ActiveSheet.Range(x(i)).Resize(, lc)
    Next
End Sub
 
Last edited:
Upvote 0
If the column does not already contain formulas that need to be retained, then try
Code:
Sub Fill_Blanks()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[1]C"
    .Value = .Value
  End With
End Sub
 
Upvote 0
I think this
Code:
Sub fill_empty()
Set ws = ActiveSheet
    lr = ws.UsedRange.Rows.Count
    lc = ws.UsedRange.Columns.Count
    x = Range("a1:a" & lr).SpecialCells(xlCellTypeBlanks).Address
    x = Split(x, ",")
    For i = 0 To UBound(x)
        ws.Range(x(i)).Offset(-1).Resize(, lc).Copy ws.Range(x(i)).Resize(, lc)
    Next
     ws.Range(x(i - 1)).Offset(1).Resize(, lc).Copy ws.Range(x(i - 1)).Offset(2).Resize(, lc)
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top