# Macro is not adding blank rows



## Ghais Chatila (Jan 4, 2023)

Hello,

i ran this macro. everything in the first part is executing correctly, and without error, but i am not getting any empty rows inserted between each distinct value in cell A

Sub AC_tab_Matchup()
'
' AC_tab_Matchup Macro
'
    ActiveCell.Range("A1:A31").Select
    Selection.Cut
    ActiveCell.Offset(0, -1).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 11).Range("A1:A31").Select
    Selection.Cut
    ActiveCell.Offset(0, -10).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 12).Range("A1:A31").Select
    Selection.Cut
    ActiveCell.Offset(0, -10).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 1).Range("A1:I31").Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(1, -3).Range("A1:A30").Select
    Selection.NumberFormat = "m/dd/yy;@"
    ActiveCell.Offset(0, 2).Range("A1:A30").Select
    Selection.NumberFormat = "m/dd/yy;@"
    ActiveWindow.LargeScroll ToRight:=-1
    ActiveCell.Offset(0, -6).Range("A1:G30").Select
    ActiveWorkbook.Worksheets("AC").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("AC").Sort.SortFields.Add2 Key:=ActiveCell.Range( _
        "A1:A30"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("AC").Sort
        .SetRange ActiveCell.Range("A1:G30")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Sub InsertRowsAtValueChange()
'Update by Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If
Next
Application.ScreenUpdating = True
End Sub


----------



## Coyotex3 (Jan 4, 2023)

For the second part of your code, try using this instead.

```
Sub InsertRows()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Insert
Next i
End Sub
```

Are you able to share an example of what your data looks like and what the expected results are?


----------



## Ghais Chatila (Monday at 12:25 PM)

I tried replacing the second part with your code, it still didn't add the blank rows. This is the result of my macro. its only missing the blank rows between AC1 & AC3. Thanks


----------



## Coyotex3 (Monday at 12:38 PM)

Ghais Chatila said:


> I tried replacing the second part with your code, it still didn't add the blank rows. This is the result of my macro. its only missing the blank rows between AC1 & AC3. Thanks
> 
> View attachment 82360


I'm a bit confused, did the second part of the code not work at all? Or did it just fail to insert a blank row between rows 29259 & 29260?


----------



## Ghais Chatila (Monday at 12:43 PM)

Coyotex3 said:


> I'm a bit confused, did the second part of the code not work at all? Or did it just fail to insert a blank row between rows 29259 & 29260?


the result of the macro is as i showed in the snapshot. it just didn't insert a blank row. the 2nd part of the code i was referring to in my original post did work by itself, i just don't know why not now.


----------



## Coyotex3 (Monday at 12:45 PM)

Ghais Chatila said:


> the result of the macro is as i showed in the snapshot. it just didn't insert a blank row. the 2nd part of the code i was referring to in my original post did work by itself, i just don't know why not now.


It is hard for most of us to be able to help without seeing an example of the data. Do all the columns end in row 29260 etc. It would be a lot easier to help if you could provide a sample of what the initial data looks like and what the expected results are. It is much easier for us if you provided a sample using XL2BB.

The part I provided is working as intended on my end.

Book12AB1AC2AD3AE4AF5AG6AH7AJSheet1

Book12AB1AC23AD45AE67AF89AG1011AH1213AJ14Sheet1


----------



## Ghais Chatila (Monday at 1:21 PM)

i am trying to follow the instructions for xL2BB, but here's what i see...


----------



## Coyotex3 (Monday at 2:26 PM)

Ghais Chatila said:


> i am trying to follow the instructions for xL2BB, but here's what i see...
> 
> View attachment 82364


Does closing and reopening Excel help at all as it pertains to XL2BB?

I can't replicate your issue as the 2nd part of the Macro works on my end(with a slight alteration).


```
Sub InsertRows()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 3 Step -1
    If Range("A" & i).Value <> Range("A" & i - 1).Value Then Rows(i).Insert
Next i
End Sub
```


Book13AB1BaanwarehouseArtosTransType2AC1PO/RCEIVING_NEW3AC1PO/RCEIVING_NEW4AC1PO/RCEIVING_NEW5AC1PO/RCEIVING_NEW6AC1PO/RCEIVING_NEW7AC1PO/RCEIVING_NEW8AC1PO/RCEIVING_NEW9AC3PO/RCEIVING_NEWSheet1

Book13ABC1BaanwarehouseArtosTransType2AC1PO/RCEIVING_NEW3AC1PO/RCEIVING_NEW4AC1PO/RCEIVING_NEW5AC1PO/RCEIVING_NEW6AC1PO/RCEIVING_NEW7AC1PO/RCEIVING_NEW8AC1PO/RCEIVING_NEW910AC3PO/RCEIVING_NEWSheet1


----------

