Not Inserting Blank Rows Between Data as Expected

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this range of data ..

Excel 2010
R
13CR
14CR
15CR
16CR
17DR
18DR
19DR
20DR
21DR
22DR
38FR
39FR
MasterWKSH
(I have intentionally hidden some rows for the ease of presentation)

I need to insert blank rows between two different values in R. For example, between rows 16 and 17 (CR and DR) and 22 and 23 (DR and FR). I am using this code:

Code:
'** INSERT SEPARATOR ROWS
    
        Dim r As Long, mcol As String, h As Long

        ' find last used cell in Column A
        r = Cells(Rows.Count, "R").End(xlUp).Row

        ' get value of  last used cell in column A
        mcol = Cells(r, 1).Value

        ' insert rows by looping from bottom
        For h = r To 2 Step -1
            If Cells(h, 1).Value <> mcol Then
                mcol = Cells(h, 1).Value
                Rows(h + 1).Insert
            End If
        Next h

The result is a row inserted between every between all rows. Every second row is empty. Data row, empty row, data row, empty row etc...
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,214,400
Messages
6,119,284
Members
448,885
Latest member
LokiSonic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top