Consulting

Results 1 to 2 of 2

Thread: Delete row on value of active cell and ac offset

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Delete row on value of active cell and ac offset

    I have a userform listbox which, when an item is selected, autopopulates column b with the item chosen and column c with the cost of the item. The end user then decides how many of the item to order for the months of Jan, Feb and march and calculaitons are based on item cost * number, etc. I would ideally like to accomplish two things....

    1) I would like the listbox to autopopulate the next available row in column b. As it is now cell in which the cursor is placed is the cell which receives the value.

    and

    2) If the end user decides that they have chosen something wrong in columb b, upon deleting the item the entire row would clear so that the end user does not have to arrow over and manually delete each of he column entries.

    I have tried two things for part of this:
    [VBA]
    with target
    application.enableevents=false
    if target.column = 1 and target.value <> "" then _
    .offset(0,1).value = TheValue
    application.enableevents=true
    end with
    [/VBA]

    and i also tried (this worked for a while but then stopped working and i can't fix it for some reason).
    [VBA]
    Dim vCellValue As Variant
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If target.Row = 1 then Exit Sub 'row 1 usually has headings
    If Target.Column = 1 Then
    If (ActiveCell.Value <> vCellValue) And (ActiveCell.Value = "") Then
    ActiveCell.EntireRow.Delete xlShiftUp
    ' or if t ActiveCell.EntireRow.ClearContents
    End If
    End If
    End Sub
    [/VBA]

    If anyone has any ideas or direction I would be very grateful. Thank you in advance.
    Last edited by Airborne; 10-04-2005 at 01:48 AM. Reason: added [VBA][/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •