How to repeat a cell value until new value is seen or reached in Excel?
As the left screenshot shown, you need to repeat the cell value until new value is seen or reached in a column in Excel. In this case, you need to repeat value A in the following blank cell until value B is reached, and repeat value B until value C is seen. How to achieve it? This article will help you.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
You can use formulas to repeat a cell value until new value is seen in Excel. Please do as follows.
1. In a new column, select a blank cell which is adjacent to the cell (E2) you need to repeat its value, then enter formula =E2 into the Formula Bar and press the Enter key. See screenshot:
2. Select the next cell (F3) in the help column, enter formula =IF(E3="",F2,E3) into the Formula Bar then press the Enter key.
3. Keep selecting cell F3, drag the Fill Handle down to repeat all cell values until new value is seen. See screenshot:
Besides the above formula, you can select all blank cells at first, and then reference all above values to fill the blank cells with shortcut keys.
1. Select the column you need to repeat cell value, then press the F5 key to open the Go To dialog box, then click Special button.
2. In the Go To Special dialog box, select the Blanks option and then click the OK button. See screenshot:
3. Now all blank cells in selected column are selected, please enter an equal sign =, press the up arrow key once, and then press the Ctrl + Enter keys simultaneously.
Then you can see the cell value in selected column is repeated until new value is seen as below screenshot:
The following VBA code can also help you to repeat a cell value until new value is seen in a certain column in Excel.
1. Select the column range you need to repeat cell value until new value is seen, then press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Module window.
VBA code: Repeat a cell value until new value is seen in Excel
Sub FillDown() Dim xRng As Range Dim xRows As Long, xCols As Long Dim xRow As Integer, xCol As Integer Set xRng = Selection xCols = xRng.Columns.CountLarge xRows = xRng.Rows.CountLarge For xCol = 1 To xCols For xRow = 1 To xRows - 1 If xRng.Cells(xRow, xCol) <> "" Then xRng.Cells(xRow, xCol) = xRng.Cells(xRow, xCol).Value If xRng.Cells(xRow + 1, xCol) = "" Then xRng.Cells(xRow + 1, xCol) = xRng.Cells(xRow, xCol).Value End If End If Next xRow Next xCol End Sub
3. Press the F5 key to run the code. Then all blank cells in selected column range are filled with value above immediately.
This section will introduce the Fill Blank Cells utility of Kutools for Excel. With this utility, you can easily repeat cell value until new value is seen with only several clicks.
1. Select the column range you need to repeat cell value, then click Kutools > Insert > Fill Blank Cells. See screenshot:
2. In the Fill Blank Cells dialog box, select the Based on values in the Fill with section, choose Down option in the Options section, and finally click the OK button. See screenshot:
Then all blank cells are filled with above cell values immediately as below screenshot shown.
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!
- How to repeat a character n times in a cell in Excel?
- How to repeat rows on top of every printout except the last page in Excel?
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 24 days agoUnter Office 365 (Excel 2016) funktioniert dieses Kutools for Excel nicht, um die Zellenwerte zu wiederholen und Excel hängt sich auf und reagiert nicht mehr
To post as a guest, your comment is unpublished.· 6 months agoDim xRow As Long, xCol As Long
if you have a very large dataset
To post as a guest, your comment is unpublished.· 10 months agoThanks a lot , fantastic