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

 

[santhoshlk]

friends.... pls help me

 

[ZVI]

Thanks for the posting sheet’s layout.
There are hidden rows below the 2nd row in the picture you've provided.
Those rows are not deleted. Cells in hidden rows are always present and even editable.
You can go to it manually by typing the hidden cell address with pressing Enter in the name field which is on the left side from fx formula editing field.
I’ve asked about it in the post#7 question #1:

… or the other rows are just hidden?

But never mind
Put the below code into ThisWorkbook module, save workbook, reload it and play with your sheet.

' All code should be in Thisworkbook module
Option Explicit
Private Sub Workbook_Activate()
  Dim m As String
  m = Me.CodeName & ".MyReturn"
  With Application
    .MoveAfterReturn = False
    .OnKey "~", m
    .OnKey "{RETURN}", m
  End With
End Sub

Private Sub Workbook_Deactivate()
  With Application
    .MoveAfterReturn = True
    .OnKey "~"
    .OnKey "{RETURN}"
  End With
End Sub

Private Sub MyReturn()
  Dim c&, i&, r&, vr&, x
  Dim ok As Boolean
  With ActiveCell
    c = .Column
    r = .Row
    ' Calc last visible row
    vr = .Parent.Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count
    ' Process last visible row
    If r = vr Then
      'Check the last row ready state
      ok = True
      For Each x In .EntireRow.Columns("A:D").Value
        i = Len(Trim(x))
        If i = 0 Then ok = False: Exit For
      Next
    End If
    ' Provide navigation
    If ok Then
      ' Insert the new row
      .Offset(1).EntireRow.Cells(1).Select
      Selection.EntireRow.Insert
    ElseIf c = 4 Then
      ' Go to the 1st cell of the next row
      .Offset(-(r <> vr), -3).Select
    Else
      ' Go to the right side cell
      .Offset(, 1).Select
    End If
  End With
End Sub

 

[santhoshlk]

hi

thanks a lot for the cide

but i press after feeding the data... i doesn't insert row. nothing happens... pls help

 

[ZVI]

You can download the working example from this link: For_Santhoshlk.xls
Macro should be allowed in Excel.
The new bottom row will be inserted only in case the last visible row is completely populated.
Typing the Enter key navigates active cell from left to right and from top to bottom.

 

[santhoshlk]

hi zvi... I REALLY APRECIATE YOUR VALUABLE EFFORT AND SUPPORT... THANKS A LOT

 

[ZVI]

You are welcome Santhoshlk,
Have a nice day!