PDA

View Full Version : Delete row on value of active cell and ac offset



thekneeguy
10-02-2005, 06:50 PM
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:

with target
application.enableevents=false
if target.column = 1 and target.value <> "" then _
.offset(0,1).value = TheValue
application.enableevents=true
end with


and i also tried (this worked for a while but then stopped working and i can't fix it for some reason).

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


If anyone has any ideas or direction I would be very grateful. Thank you in advance.

NashProjects
12-23-2011, 01:55 AM
populating the next available cell


dim dRow as double
dRow = 2
do until cells(drow,2) = ""
drow = drow+1
loop
cells(drow,2) = dcolumnBValue
cells(drow,3) = columnCValue


Deleting the section when the cursor has selected the cell it wants to delete

range("B"& activecell.row &":C"& activecell.row &"").delete xlup


Hope that helps!!