If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > insert a blank row macro

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-24-10, 17:54
Dalman71 Dalman71 is offline
Registered User
 
Join Date: Apr 2009
Posts: 6
Question 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.
Reply With Quote
  #2 (permalink)  
Old 03-25-10, 05:32
Colin Legg Colin Legg is offline
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 495
Hi Dalman,

There are a few ways to do this. Here's an example:
Code:
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)
                Else
                    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...
__________________
Colin

RAD Excel Blog

Other tutorials:
Array Formulas | Deleting Rows with VBA
Reply With Quote
Reply

Tags
insert blank row

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On