Macro is not adding blank rows

[Ghais Chatila]

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]

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]

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]

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]

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]

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.

Book12
	A	B
1	AC
2	AD
3	AE
4	AF
5	AG
6	AH
7	AJ
Sheet1

Book12
	A	B
1	AC
2
3	AD
4
5	AE
6
7	AF
8
9	AG
10
11	AH
12
13	AJ
14
Sheet1

 

[Ghais Chatila]

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

 

[Coyotex3]

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

Book13
	A	B
1	Baanwarehouse	ArtosTransType
2	AC1	PO/RCEIVING_NEW
3	AC1	PO/RCEIVING_NEW
4	AC1	PO/RCEIVING_NEW
5	AC1	PO/RCEIVING_NEW
6	AC1	PO/RCEIVING_NEW
7	AC1	PO/RCEIVING_NEW
8	AC1	PO/RCEIVING_NEW
9	AC3	PO/RCEIVING_NEW
Sheet1

Book13
	A	B	C
1	Baanwarehouse	ArtosTransType
2	AC1	PO/RCEIVING_NEW
3	AC1	PO/RCEIVING_NEW
4	AC1	PO/RCEIVING_NEW
5	AC1	PO/RCEIVING_NEW
6	AC1	PO/RCEIVING_NEW
7	AC1	PO/RCEIVING_NEW
8	AC1	PO/RCEIVING_NEW
9
10	AC3	PO/RCEIVING_NEW
Sheet1