insert row

straus

Board Regular
Joined
Oct 7, 2002
Messages
192
Hello,

In the database below, I want to a row to be inserted before every Row where the credit class is "I". Can this be done with the help of a macro? Pls help

CR. CLASS A/c No. Amnt Status
I 1280021514 2431500 active
G 1280035621 3454353 not active
G 3580026521 3454387 active
G 9580025141 9879898 not active
S 1280049936 3621548 not active
D 3580026214 242342 not active
I 8880022221 4334355 active
P 1218005075 34544 not active
Q 1597999218 44414 not active
Q 1977997360 495694 not active
I 2357996503 54724 active
R 2737994645 59879 not active
I 3117993788 65034 not active
R 4564564564 701 active
R 8564564564 75344 not active
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
Although I don't do VBA, this is the code produced when I recorded a macro with your data. It seems to work. Others may offer more expert advice, of course.
' Keyboard Shortcut: Ctrl+i
'
Cells.Find(What:="I", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Selection.EntireRow.Insert
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
 

attc

Board Regular
Joined
Oct 11, 2002
Messages
87
this is similar to a problem I had, except instead of inserting a row, I wanted to make things bold, underlined and other stuff. you might be able to use something like this:

Sub InsertRow()
'
Application.ScreenUpdating = False

Dim FOUND As Range
Dim firstR As Integer

Set FOUND = Cells.Find(What:="I", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Let firstR = FOUND.Row + 1

Do
FOUND.Activate
FOUND.EntireRow.Insert

Set FOUND = Cells.Find(What:="I", After:=ActiveCell.Offset(1, 0), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

Loop Until (FOUND.Row = firstR)

End Sub


[warning:] this might not be particularly robust, e.g. if there are NO "I"s or there are some credit classes consisting of more than 1 character that could also include an "I", etc.

ps. I just realised also that it inserts a row before any row where there is an "I" and not just in col.A
This message was edited by attc on 2002-10-26 13:31
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
Hi Straus,

Try this simple macro:
<pre>
Sub insertRowsWhereCreditclassIsI()
Dim row As Long
row = 2 'skip headings
While Cells(row, 1) <> ""
If UCase(Cells(row, 1)) = "I" Then
Rows(row).Insert Shift:=xlDown
row = row + 1
End If
row = row + 1
Wend
End Sub
</pre>
 

straus

Board Regular
Joined
Oct 7, 2002
Messages
192
that was superb..Rickrack...just what I wanted...you guys are great...thanks a ton

gratefully yours,
straus
 

Watch MrExcel Video

Forum statistics

Threads
1,127,035
Messages
5,622,332
Members
415,894
Latest member
silverhaze

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