Solved: First available row and clear row

[thekneeguy]

I just have one more thing to do to complete my excel project. I have a user form that the end user selects and item. The OK command button places that item and the corresponding cost into the first column and the second column (B and C). The user then enters across the row how many of those items they want for each month.

I would like to add code to the command button so that it automatically finds the next available row to enter the item/cost AND I would love it so that if the item is deleted from column B all other entries in that row would clear to save the user from tabbing and deleting for several columns.

I have had a couple of people suggest different things but unfortunately they were sort of half answers and I think they presumed that I knew more about coding that I really do, which is novice level.

Could someone please help or direct me toward info to read?

Thank you for any help and/or direction.

[malik641]

Hey thekneeguy, this should get you started.

For the userform:

[VBA]
Private Sub OKButton_Click()
Dim iLastRow As Long
With ActiveSheet
iLastRow = Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
Cells(iLastRow, 2).Value = txtItem.Value
Cells(iLastRow, 3).Value = txtPrice.Value
End With
End Sub[/VBA]
This finds the last row in column B and uses the next row to enter the info for the Item (in Column B) and the Price (in column C).

And if you want to clear the row when item is deleted, place this in the worksheet module where the items are kept:
[VBA]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target(1, 1) <> 0 Then Exit Sub
If Target(1, 1).Column = 2 Then Target(1, 1).EntireRow.ClearContents
End Sub
[/VBA] This ought to be enough to get you going (I'm sure you'll modify it to what you fully need). Good luck!

Edit: If you want to delete the whole row and have it shift up then use:

[VBA]
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target(1, 1) <> 0 Then Exit Sub
If Target(1, 1).Column = 2 Then Target(1, 1).EntireRow.Delete Shift:=xlUp
End Sub
[/VBA]

[Zack Barresse]

Joseph, just curious, why ...

[vba] With ActiveSheet[/vba]

?? Seems rather redundant to me, as it's not needed. Any particular reason you use it that way?

Also ..

[vba]... Shift:=xlUp [/vba]
.. isn't even needed either, it's implied, as is Activesheet.

[malik641]

Joseph, just curious, why ...

[vba] With ActiveSheet[/vba]

?? Seems rather redundant to me, as it's not needed. Any particular reason you use it that way?

Also ..

[vba]... Shift:=xlUp [/vba]
.. isn't even needed either, it's implied, as is Activesheet.

Hmmm.....

For the "...Shift:=xlUp", I forgot that was implied

...And I thought I had a reason for the ActiveSheet...But now that I think about it I meant something else. I meant to name the worksheet as to where the values will be stored just incase the button to activate the userform is Not on the sheet where the values will be placed.

So instead of ActiveSheet I meant:
[VBA] With Worksheets("Sheet_Name")[/VBA]

My bad! Thanks for noticing Zack I guess I was rushing to write the code and wasn't really thinking about that

[Zack Barresse]

No problem Joseph. Glad I didn't offend you, as it wasn't the purpose.

Great post!

[malik641]

Glad I didn't offend you, as it wasn't the purpose.

I knew your intentions were meant to inform and not to offend. I know you're better than that Zack!

[thekneeguy]

Joseph,

Thank you GREATLY for this! This was exactly what I needed. I modified the code a bit, of course, to go with my unload macro but it worked GREAT and the clear row IS PERFECT!!!!!!!!!!!! THANK YOU THANK YOU THANK YOU. It really adds another addon to my program

I don't know how to mark this solved (I have another post just below this that needs marked as well.) How do I do this?

[malik641]

Joseph,

Thank you GREATLY for this! This was exactly what I needed. I modified the code a bit, of course, to go with my unload macro but it worked GREAT and the clear row IS PERFECT!!!!!!!!!!!! THANK YOU THANK YOU THANK YOU. It really adds another addon to my program

I don't know how to mark this solved (I have another post just below this that needs marked as well.) How do I do this?

Hey no problem! Glad to help!

And to mark a thread solved, just go to the top of the thread and click "Thread Tools" and choose "Mark Thread Solved" and click "Perform Action"