• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Insert blank row macro

M

Matthew Rich

New Member
Looking for some help with a macro which seems to be a little out of my area of knowledge.
I have a set of data that I would like to insert a blank row in if a certian value in a column satisfies the requirment. The column contains a product code that each line item has. I intially do a custom sort by client A to Z, then by the product code. Problem is that the groups aren't always the same Client A could have five rows of product BNM and the next Client could have 1 row of prodduct TIV. So I'd like to have a blank row inserted after each grouping wether that be 1 or 10 of the poduct code groupings. Hopefully this makes since, not the best at explaining this out.
 
Matthew Rich said:
I can't upload sorry. Maybe I can explain it if you let me know what your are looking for.
Click to expand...
Hi,

Try this code, you will need to change the column we're looking at and then run the code.

Code: 
Sub insertrowifnamechg()
Dim MyColumn As String, x As Long
'Change to the correct column
MyColumn = "A"
For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
  If Cells(x - 1, MyColumn) <> Cells(x, MyColumn) Then Rows(x).Insert
Next x
End Sub
 
Try this then:
Code: 
Sub InsertBlanks()
Dim lastRow As Long, i As Long
Dim myColumn As String

'Which column are we looking at?
myColumn = "M"

Application.ScreenUpdating = False
lastRow = Cells(Rows.Count, myColumn).End(xlUp).Row

For i = lastRow - 1 To 2 Step -1
    If Cells(i, myColumn).Value <> Cells(i + 1, myColumn).Value Then
        Cells(i + 1, myColumn).EntireRow.Insert
    End If
Next i
Application.ScreenUpdating = True

End Sub

EDIT: I see Mike H was thinking along the same lines as me, but was faster on the response. :)
 
Matthew Rich said:
It would be column M.
Click to expand...
Hi,

Then all you do is change the column like this

Code: 
Sub insertrowifnamechg()
Dim MyColumn As String, x As Long
MyColumn = "M"
For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
  If Cells(x - 1, MyColumn) <> Cells(x, MyColumn) Then Rows(x).Insert
Next x
End Sub
 
Would be good to have Application.Screenupdating bit as in Luke's code. That will speed up the processing by stopping screen flicker.
 
shrivallabha said:
Would be good to have Application.Screenupdating bit as in Luke's code. That will speed up the processing by stopping screen flicker.
Click to expand...
Thanks,

I would also switch calculation to manual during execution.

Code: 
Sub insertrowifnamechg()
With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
Dim MyColumn As String, x As Long
MyColumn = "M"
For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
  If Cells(x - 1, MyColumn) <> Cells(x, MyColumn) Then Rows(x).Insert
Next x
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
End With
End Sub
 
Mike H.. said:
Hi,

Try this code, you will need to change the column we're looking at and then run the code.

Code: 
Sub insertrowifnamechg()
Dim MyColumn As String, x As Long
'Change to the correct column
MyColumn = "A"
For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
  If Cells(x - 1, MyColumn) <> Cells(x, MyColumn) Then Rows(x).Insert
Next x
End Sub
Click to expand...


That worked perfect! Thanks!

Let me ask another question. Instead of having a refernce column in my case "M" could I have it refernce the title of the sort filter? So if in the future I add more columns and it moves the specific data set out of column "M" it could still work without changing the ref column?
 
Matthew Rich said:
That worked perfect! Thanks!

Let me ask another question. Instead of having a refernce column in my case "M" could I have it refernce the title of the sort filter? So if in the future I add more columns and it moves the specific data set out of column "M" it could still work without changing the ref column?
Click to expand...
Hi,

Yes that's easily done, see the code below. I hope I'm not breaking a forum rule here but see this line from the code.

MyColumn = Application.Match("Header 10", ActiveSheet.Rows(1), 0)

You would need to change the bold bit to the correct header name which I have assumed is in row 1.

Note as a result of this change we now DIM MyColumn as LONG and NOT STRING

Code: 
Sub insertrowifnamechg()
With Application
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
Dim MyColumn As Long, x As Long
MyColumn = Application.Match("Header 10", ActiveSheet.Rows(1), 0)
For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
  If Cells(x - 1, MyColumn) <> Cells(x, MyColumn) Then Rows(x).Insert
Next x
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
End With
End Sub
 
Matthew Rich said:
I changed the "Header 10" to exactly what mine says, but I'm getting a runtime error '13'.
Click to expand...

Hmmm,

That almost certainly means the code can't find the header.

Are you sure you correctly entered the header name in the code?
The name isn't case sensitive.
One thing that regularly trips people up is leading/trailing spaces. Check for these in your header.
The code looks for the header in Row 1 of the worksheet, is that correct? If it's in a different row then see my previous post and change the 1 in bold to the correct row number.
 
Nevermind that was my fault. Went back and looked at the header title and didn't realize there was a space in the front from when it exported. Thanks again for your help!
 
You must log in or register to reply here.
Back
Top