Insert Blank Rows Before Repeated Site Names with Excel VBA
Question details
Insert a blank row above every occurrence of a listed site name after its first occurrence in column A.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A worksheet contains site names in column A and a separate list identifies the names to process.
- Observed behavior
- Each listed site's first occurrence should remain unchanged, while later occurrences should have a blank row inserted above them.
Place the data on a sheet named Data and the unique site list on a separate sheet named SiteList, with headers in row 1 and names in column A. Make sure the name cells contain text rather than errors, and save the workbook as an Excel Macro-Enabled Workbook (.xlsm).
Run a VBA macro that counts sites and inserts rows from the bottom
Use this method to process listed site names, including names that appear in nonconsecutive rows.
The macro counts matching names first, then works upward from the last data row. It inserts a row only when an earlier occurrence remains, so the first occurrence is left untouched.
Matching ignores leading and trailing spaces and is case-insensitive. Blank cells and names absent from SiteList are ignored.
In Excel, open the workbook and press Alt+F11. In the VBA editor, select Insert > Module.
Paste this code into the module: Sub InsertRowsBeforeRepeatedSites() Dim ws As Worksheet, listWs As Worksheet Dim allowed As Object, counts As Object Dim lastRow As Long, lastListRow As Long, r As Long Dim site As String Set ws = ThisWorkbook.Worksheets("Data") Set listWs = ThisWorkbook.Worksheets("SiteList") Set allowed = CreateObject("Scripting.Dictionary") Set counts = CreateObject("Scripting.Dictionary") allowed.CompareMode = vbTextCompare counts.CompareMode = vbTextCompare lastListRow = listWs.Cells(listWs.Rows.Count, "A").End(xlUp).Row For r = 2 To lastListRow site = Trim$(CStr(listWs.Cells(r, "A").Value2)) If Len(site) > 0 Then If Not allowed.Exists(site) Then allowed.Add site, True End If Next r lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For r = 2 To lastRow site = Trim$(CStr(ws.Cells(r, "A").Value2)) If Len(site) > 0 Then If allowed.Exists(site) Then If Not counts.Exists(site) Then counts.Add site, 0 counts(site) = counts(site) + 1 End If End If Next r For r = lastRow To 2 Step -1 site = Trim$(CStr(ws.Cells(r, "A").Value2)) If Len(site) > 0 Then If counts.Exists(site) Then If counts(site) > 1 Then ws.Rows(r).Insert counts(site) = counts(site) - 1 End If End If Next r End Sub
Return to Excel, open Developer > Macros, select InsertRowsBeforeRepeatedSites, and click Run. Enable macros for the workbook if prompted and you trust its contents.
On the Data sheet, check that each listed site's first occurrence has no newly inserted row above it and that every later occurrence does.

Insert Rows for Repeated Sites in WPS Spreadsheet
Open the workbook in WPS Spreadsheet and run the same macro if your WPS installation supports VBA. VBA availability can depend on your version and installed components.
- 1. Open the workbook: In WPS Spreadsheet, open the XLSM workbook and confirm that Data and SiteList contain the site names in column A.
- 2. Check VBA availability: Look for Developer > Visual Basic or a VBA editor in your WPS installation. If it is unavailable, check whether your version supports the required VBA components.
- 3. Add and run the macro: In the VBA editor, choose Insert > Module and paste the macro above. Use Developer > Macros to select and run InsertRowsBeforeRepeatedSites.
- 4. Review and save: Check the inserted rows on Data, then use File > Save As to retain the workbook in XLSM format.

Frequently Asked Questions
Does the macro work when repeated site names are not next to each other?
Yes. It counts each listed name across column A before inserting rows, so matching names do not need to be consecutive.
How does the macro identify the first occurrence?
As it scans from the bottom up, the macro reduces the count for each name. When only one occurrence remains, it leaves that row unchanged.
What happens to names that are not on the SiteList sheet?
They are ignored. Add a name to SiteList column A before running the macro if you want its repeated occurrences processed.
Does the site-name match distinguish uppercase and lowercase letters?
No. The dictionaries use case-insensitive matching, and the macro trims spaces from the beginning and end of each name before comparing it.
Can I run the macro again after adding more records?
You can, but it will insert additional blank rows above existing repeated occurrences as well. Remove or otherwise account for the previously inserted rows before rerunning it.




