inserting rows

[amberlabi]

I need to insert a blank row between each row i have in my s/sheet so i can import data into MYOB. Is there a quick way to do this?

 

[venkat1926]

save your file safely somewhere and run this sub


Public Sub test()
Dim i As Integer
Dim j As Integer
j = Range("a1").End(xlDown).Row
i = 2
line1:
Rows(i).Insert
i = i + 2
If Range("a" & i) <> "" Then GoTo line1
End Sub

 

[Smitty]

Welcome to the Board!

See how this works for you:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> InsertRows()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">For</SPAN> x = ActiveSheet.UsedRange.Rows.Count <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
                Cells(x, 1).EntireRow.Resize(1).Insert
            <SPAN style="color:#00007F">Next</SPAN> x
        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty

 

[amberlabi]

thanks guys - all done!

love your work...

 

[Nimrod]

The following solution is not dependent on the values being in any particular column. Nor does it require the data to start at the top of the sheet . :wink:

Public Sub InsertBlankRows()
    BotRow = 65536
    Do
    With ActiveSheet.Range("a1:iv" & BotRow)
        Set c = .Find("*", SearchDirection:=xlPrevious, searchorder:=xlColumns)
            If Not c Is Nothing Then
                    firstRow = c.Row
                    c.EntireRow.Insert
                    BotRow = c.Row - 1
            End If
        End With
     Loop While Not c Is Nothing
End Sub

 

[hanasamo]

Welcome to the Board!

See how this works for you:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> InsertRows()
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
            <SPAN style="color:#00007F">For</SPAN> x = ActiveSheet.UsedRange.Rows.Count <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
                Cells(x, 1).EntireRow.Resize(1).Insert
            <SPAN style="color:#00007F">Next</SPAN> x
        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Hope that helps,

Smitty

I'm new to VBA programming, can you explain to me how "x = ActiveSheet.UsedRange.Rows.Count 1 to -1" works?

 

[Nimrod]

Hello hanasamo


Each of the loops on this thread/post use a different method of finding out where the bottom of the sheet is so that it knows what rows to loop thru.

Below i've created a macro that will demostrate the 3 methods . Each method will display what it thinks is the bottom row ... :wink:

WARNINIG : This macro should be used on a blank sheet since it adds sample data to the sheet.

Public Sub demo()
' place some values in sheet
For rw = 5 To 40 Step 2
    col = col + 1
    If col > 4 Then col = 1
    
    Cells(rw, col).Value = rw
Next rw

' NOW SHOW DIFFERENT TECHNIQUES FOR FIND BOTTOM

    'ONLY GIVE TRUE BOTTOM IF NO EMPTY ROWS AT TOP
    MsgBox ActiveSheet.UsedRange.Rows.Count
    
    ' Must specify which row to look in
    MsgBox Cells(65536, 1).End(xlUp).Row
    
   
   ' Will find bottom row of any column
    MsgBox Range("a1:iv65536").Find("*", SearchDirection:=xlPrevious)
    
    
End Sub