Inserting and Deleting row automatically

[santhoshlk]

Dear experts..

kindly help me this...

i have a spread sheet to inout customer names only.

i need to have only 2 rows in this is sheet and & while you hit enter key after feeding the customer a new row should appear below to the previous filed so i can feed the next customer.. and also it should shring whenecer i delete a custmer from the bottom....

i dont know whether this is possible or not in excel...

HOPE MR EXCELS CAN DO IT

 

[amir_urmila]

Hi Santosh - You can change the setting in Excel

which basically move down once the Enter button is hit

select Excel option
go to advance option

then select after pressing enter move Selection

Direction here you can select direction as Down

 

[santhoshlk]

HI THERE..

excel option doeasn't help me.... its a common option

I need to have only 2 rows in me sheets. row 1 & 2 ONLY.

after you filling the data in row2 then press enter the a new row appears to inout the data

got it???

 

[santhoshlk]

still waiting for a solution

 

[ZVI]

Not sure of details but try this:

' Copy code to the sheet's VBA module
' If A2 is modified then rows #2 is copied to rows #1,
' with cleaning of rows #2
Private Sub Worksheet_Change(ByVal Target As Range)
  Const MagicalCell = "A2"
  On Error Resume Next
  If Intersect(Target, Range(MagicalCell)) Is Nothing Then Exit Sub
  Application.EnableEvents = False
  With Rows(1).CurrentRegion.Rows(1)
    .Value = .Offset(1).Value
    .Offset(1).ClearContents
  End With
  Target.Select
  Application.EnableEvents = True
End Sub

 

[santhoshlk]

thanks for ur help ZVI...

but my rqst is like this...

i have only 2 rows when i open my book.
so i type in row#1 and then row# and press enter. then i new row will be added and allowing me to eneter tha data... like goes on

suppose i eneterd 5 rows. and if i delete the contetnt of the last row then that row should be removed and now i have only 4#

pls help

 

[ZVI]

There were "only 2 rows in my sheets" mentioned in your previous posts, but now you are talking about 4 or 5 rows.
There are too many other uncertain declarations in your task for one (shoot) step helping.

Please be more specific in declarations because terms in Excel mean something specific.

To exclude guessing please answer these questions:

1.

i have only 2 rows when i open my book.

There are always 65536 rows in a sheet of Excel 2003 and 1048576 rows in Excel 2007/2010.
Do you mean 2 rows with already populated data in it or the other rows are just hidden?

2.

... so i type in row#1...

How many columns with data are in row#1?
What cell of row#1 exactly you are talking about?

3.

... and then row# and press enter.

Could you please post example of the cell content before and after typing?

4.

... then new row will be added

Where?
Will new row be empty?

5.

... and allowing me to eneter tha data... like goes on

What cell of the added row will be selected?

6.

... suppose i eneterd 5 rows.

Please post example of the (what?) cell content before and after typing.

7.

... and if i delete the content of the last row

Which row is the last for you? How it will be determined?

8.

... then that row should be removed and now i have only 4#

Please clarify, do you mean deleting of entire row, or deleting (what?) cell with shifting in that row, or just clearing of row/cell content?

It is essential to post cells layout before, during and after typing.

 

[santhoshlk]

Hi friend.. let me explain clearly....

Excel has 65536 rows.... but i deleted all rows keeping only 2 rows left.

row#1 is my header of data
i have 4 columns.. my headers are A1=CODE, B1=NAME, C1=TEL NO, D1=CONTACT PERSON

now i need to enter my contacts 1 by 1

so i go to A2 and fille the data in respective columns. now i press enter key.. then excel need to insert a new row and my active cell will be A3 so i can continue adding next contact detaols. it goes like this.

 

[ZVI]

Hi Santhoshlk,

Excel has 65536 rows.... but i deleted all rows keeping only 2 rows left

The deleted rows in Excel sheet are always replaced by the empty rows.
Seems you are looking for navigation cells by the aid of pressing Enter key in A:D columns, from left to right, row by row.

If so then run the code below to create new workbook with testing sheet.
1. Open your workbook
2. Copy the code below
3. Press Alt+F11 to open the Visual Basic Editor (VBE)
4. From the menu choose Insert-Module
5. Paste the code into the code window at right (Module1)
6. Close the VBE (Alt-Q) and save the file if desired
7. Press Alt-F8 and run the macro by double-click on TestSheetCreate

New testing workbook with single sheet will be created.
Code unlocks cells in A:D columns except of A1:D1.
In Excel options the "Auto pressing Enter, move selection" will be set to "Right" direction.
Sheet will be protected with blocking selection of the locked cells.
Try pressing Enter key to see the navigation.

Let me know if the behavior of the test sheet is like the required.

Sub TestSheetCreate()
  With Workbooks.Add(xlWBATWorksheet).Sheets(1)
    With .Range("A:D")
      .Locked = False
      .Rows(1).Locked = True
      .Rows(1).Value = Split("CODE,NAME,TEL NO,CONTACT PERSON", ",")
      .Columns(4).AutoFit
    End With
    .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, _
             AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingRows:=True, _
             AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    .EnableSelection = xlUnlockedCells
  End With
  Application.MoveAfterReturnDirection = xlToRight
End Sub

 

[santhoshlk]

dears kindly see my worksheet.. it got only two rows.
and once i finisged filling the data in row #2 i will press enter key... then i new row should be added below