Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2009

    Question Unanswered: insert a blank row macro

    Hi, I have a table of data that I export from SAP into Excel 2007.
    Column D contains job numbers that are already sorted and look something like the following:

    (Row 1)Header data
    (Row 2)1111112
    (Row 3)1111112
    (Row 4)1111113
    (Row 5)1111114
    (Row 6)1111115
    (Row 7)1111115

    What I need is a macro that searches column D and inserts a blank row in between the rows where the job number changes:

    (Row 1)Header data
    (Row 2)1111112
    (Row 3)1111112
    (Row 4)
    (Row 5)1111113
    (Row 6)
    (Row 7)1111114
    (Row 8)
    (Row 9)1111115
    (Row 10)1111115

    Hope you can help... Thank you.

  2. #2
    Join Date
    Sep 2008
    London, UK
    Hi Dalman,

    There are a few ways to do this. Here's an example:
    Sub foo()
        Dim lRow As Long, lLastRow As Long
        Dim rngToInsert As Range
        lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
        For lRow = 2 To lLastRow
            With Cells(lRow, 1)
                If .Value2 <> .Offset(1).Value2 Then
                    If rngToInsert Is Nothing Then
                        Set rngToInsert = .Offset(1)
                        Set rngToInsert = Application.Union(rngToInsert, .Offset(1, lRow Mod 2))
                    End If
                End If
            End With
        Next lRow
        If Not rngToInsert Is Nothing Then
            Application.ScreenUpdating = False
            rngToInsert.EntireRow.Insert shift:=xlShiftDown
            Application.ScreenUpdating = True
        End If
    End Sub
    You can adapt it as necessary: for example, you can add more criteria to determine whether or not a row needs to be inserted.

    If you want any of the code or logic explained then please ask.

    Hope that helps...

  3. #3
    Join Date
    Feb 2012

    Insert a line break within the cell at the end of the text


    First I like to thank you.

    I am new to excel macro. I am looking for a solution which is time-sensitive.

    Issue: I like to have a line break at the end of the last character of the text within the cell of the excel file.I want to apply the line break for entire column not just one cell. Can I know the VBA code for it?

    Attached picture may give you an idea?

    Thanks in advance!

    Attached Thumbnails Attached Thumbnails 5-21-2014 2-12-50 PM.png  

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts