[Urgent] How to add Row Below or above Automatically

[funtonight]

Hi Friends,
i need lil help regarding in following sample how to add row automatically below row 4 means above the 5th row named total , and it continues to add row above TOTAL when the above row of Total gets filled . like at the moment row 4 is filled then i want excel to add row 5 automatically and Total row moves to row 6 but the formulas remain intact of total and so on

Sample of Excel Sheet am using ( IN THIS POST FORMATTING IS NOT REMAINING INTACT BUT HOPE YOU WILL UNDERSTAND:



<table border="0" cellpadding="0" cellspacing="0" width="320"><col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" height="20" width="64">Date</td> <td style="width: 48pt;" width="64">C.V No </td> <td style="width: 48pt;" width="64">Payment</td> <td style="width: 48pt;" width="64">Recipts</td> <td style="width: 48pt;" width="64">Comments</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/6/2011</td> <td align="right">5</td> <td align="right">50000</td> <td>
</td> <td>payment</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">7/6/2011</td> <td align="right">17</td> <td>
</td> <td align="right">1000</td> <td>recipt</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">13-6-2011</td> <td align="right">16</td> <td align="right">5000</td> <td>
</td> <td>POL</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">14-6-2011</td> <td align="right">18</td> <td>
</td> <td align="right">600</td> <td>OTHERS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Total</td> <td align="right">55000</td> <td align="right">1600</td> <td>
</td> </tr> </tbody></table>

i dont know much abt Macro , so please tell me in simple language and step by step procedure how to add that code to make it work

Regards!

 

[funtonight]

Some one please help

 

[krishna334]

Hi Funtonight,

i hope this macro code works...

Copy paste below code (from Sub to End Sub) to a module in VB editor in excel:
To do that press ALT+F11 (This opens the VBA Window in excel)
Go to insert---Module...then paste the below code in the blank space...


Sub Macro1()
Cells.Find(What:="total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

ActiveCell.Row.Select
Selection.Insert Shift:=xlDown

End Sub



After that press ALT+F8...SELECT Macro1 IN the pop up window..hit run.

I will explain if it works.

 

[funtonight]

hi krishna thanks for prompt reply

but its giving following error

"Compile error,invalid qualifier"

nd hight light this part

ActiveCell.Row.Select

 

[krishna334]

So Sorry for the wrong code....
Delete the old code n replace it with the below one...


Sub Macro1()
Dim i As Integer
Cells.Find(What:="total", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
i = ActiveCell.Row
Rows(i & ":" & i).Select
Selection.Insert Shift:=xlDown

Range("A1").Select

End Sub

 

[funtonight]

Thanks Krishna Code worked but following problems :

it did inserted row but that could have been inserted manually too , but what i need is that it keep inserting row automatically when the last row above TOTAL gets filled.

Secondly it didnt added the new row figures to total means formula didnt got updated in Total row


So help plz

 

[Maheshp]

Hi
i assume total string is in column A
Right click on sheet tab & click view code, paste below code in sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call test1
End Sub

copy below code & paste in vb editor module

Sub test1()
Dim Found As Range
Set Found = Columns(1).Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing And Found.Offset(-1, 0).Value <> "" Then Found.EntireRow.Insert
End Sub

 

[funtonight]

hi Maheshp Thanks for reply

your formula did added new row and did updated the formula too


BUT WHEN I FILLED THAT ROW , IT DIDNT ADDED A NEW ROW AUTOMATICALLY

i need such a formula that keep adding new row automatically when last row above Total gets filled

 

[Maheshp]

BUT WHEN I FILLED THAT ROW , IT DIDNT ADDED A NEW ROW AUTOMATICALLY

it seems you have not paste below code in sheet tab.
does you Total word is in column A
when the cell above total word , is not blank then it insert row

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call test1
End Sub

 

[funtonight]

hey mahshep your formula is working


but where is module thing


i click on sheet and click on view code

a window open that is of VB worksheet or General

and i paste the code but only 1 code works ,


KINDLY GUIDE WHERE I NEED TO PASTE FIRST CODE AND WHERE I NEED TO PASTE SECOND CODE