Supposing you want to insert texts “Name”, “Data” and “Score” to every one row in the certain range as below screenshot shown. Please try the following VBA code.
1. In the worksheet, press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Application window.
2. Click Insert > Module. See screenshot:
3. Copy and paste below VBA code into the Code editor.
VBA code: insert same text in every one row in Excel
Dim Last As Integer
Dim emptyRow As Integer
Last = Range("A" & Rows.Count).End(xlUp).Row
For emptyRow = Last To 2 Step - 1
If Not Cells(emptyRow, 1).Value = "" Then
Range(Cells(emptyRow, "A"), Cells(emptyRow, "C")).Value = Array("Name", "Date", "Sore")
Notes: In the above code:
1. Your data should be started from the first row in the worksheet.
2. Please change the texts of “Name”, “Data” and “Score” to the texts you need.
3. The letter A and C indicate the cell range where you want to insert the specific text.
4. Press the F5 key to run the code. And you can see the specified texts are added in every one row in your worksheet.
Insert same text in every other row with Kutools for Excel
With the above code, you can just insert text in every one row. How about insert text in every n row in Excel? In this section, we will show you the Insert Titles rows utility of Kutools for Excel. With this utility, insert text in every n row won’t be a problem anymore.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Thank you, the code was exactly what I needed. One question, I have data that I want repeated every other row but only to specific rows then a new string of text starts that I want repeated. How to I change the code to reflect stopping the repeat at a specific row?