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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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?
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.· 7 months 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.· 1 years agoDim xRow As Long, xCol As Long
if you have a very large dataset
To post as a guest, your comment is unpublished.· 1 years agoThanks a lot , fantastic