VBA & Macro Problems

Insert Blank Rows Before Repeated Site Names with Excel VBA

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 869 views

Question details

Insert a blank row above every occurrence of a listed site name after its first occurrence in column A.

Insert Blank Rows Before Repeated Site Names with Excel VBA
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.
Before you start

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).

Solution 1Recommended

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.

1
Open the VBA editor

In Excel, open the workbook and press Alt+F11. In the VBA editor, select Insert > Module.

2
Add the macro

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

3
Run the macro

Return to Excel, open Developer > Macros, select InsertRowsBeforeRepeatedSites, and click Run. Enable macros for the workbook if prompted and you trust its contents.

4
Check the results

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.

Run a VBA macro that counts sites and inserts rows from the bottom
Run only once: Running the macro again inserts additional blank rows. Entire worksheet rows are inserted, so other data on the Data sheet moves with them.
Work with spreadsheets in WPS Office

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. 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. 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. 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. 4. Review and save: Check the inserted rows on Data, then use File > Save As to retain the workbook in XLSM format.
Insert rows before repeated site names using the same bottom-up macro.Open and edit Microsoft Excel formats, including XLSX and XLSM; macro support varies by installation.Use familiar spreadsheet tools to review and edit the results.
QA img-9

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.