struggling with row inserts in a macro...

UKPhil

New Member
Joined
Jul 25, 2002
Messages
49
Hi what Im trying to do (with no success whatsoever) is execute a macro which will
loop down a column of data until it reaches a cells with the value "Break" in them- insert a new blank row directly above the row "Break" then continue to the bottom of the list. Help!!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

manney

New Member
Joined
Mar 25, 2002
Messages
46
There are several ways of looping. This one (While...wend) assumes you do not know how long your column of data is, and assumes the cells under the data are blank (Zero length)

Sub BreakFindLoop()

ROffset = 0
'Test for length of cells in column. Will not equal 0 if it contains data
While Len(Range("A1").Offset(ROffset, 0)) <> 0
'Test for word "Break"
If Range("A1").Offset(ROffset, 0) = "Break" Then
'Insert new row
Range("A1").Offset(ROffset, 0).EntireRow.Insert
'Move down so we aren't over new, blank cell
ROffset = ROffset + 1
End If
'Goto next line
ROffset = ROffset + 1
Wend

End Sub
 

UKPhil

New Member
Joined
Jul 25, 2002
Messages
49
Thanks Manney, but I've already got my loop working fine, the problem I have is with the actual code that will insert a blank row directly above the current ActiveCell. Anyone?
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Howdy Phil, the following should shif cells with the value "break" in the active column down.

<pre>
Sub InsrtRw()
Dim c As Range, fst As Range, c2 As Range
On Error GoTo errHnd
Set c = ActiveCell.EntireColumn.Find("Break", MatchCase:=False, _
lookat:=xlPart, LookIn:=xlValues)
If Not c Is Nothing Then
c.EntireRow.Resize(1).Insert
Set fst = c
again:
Set c2 = c.EntireColumn.FindNext(c)
If Not c2 Is Nothing Then
If c2.Address <> c.Address _
And c2.Address <> fst.Address Then
c2.EntireRow.Resize(1).Insert
Set c = c2
GoTo again
End If
End If
End If
Exit Sub
errHnd: MsgBox "You've tried to shift data off the worksheet," _
& " please reconsider your worksheets layout."
End Sub</pre>

Different kind of loop... Hope this helps.
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
Phil, if you want the row above shifted down as well, use an index:

<pre>
Sub InsrtRw2()
Dim c As Range, fst As Range, c2 As Range
On Error GoTo errHnd
Set c = ActiveCell.EntireColumn.Find("Break", MatchCase:=False, _
lookat:=xlPart, LookIn:=xlValues)
If Not c Is Nothing Then
c(0).EntireRow.Resize(1).Insert
Set fst = c
again:
Set c2 = c.EntireColumn.FindNext(c)
If Not c2 Is Nothing Then
If c2.Address <> c.Address _
And c2.Address <> fst.Address Then
c2(0).EntireRow.Resize(1).Insert
Set c = c2
GoTo again
End If
End If
End If
Exit Sub
errHnd: MsgBox "You've tried to shift data off the worksheet," _
& " please reconsider your worksheets layout."
End Sub</pre>
 

Forum statistics

Threads
1,144,741
Messages
5,726,014
Members
422,653
Latest member
mntsiki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top