userform vba (credit control list)

apocalypticdon

New Member
Joined
Oct 4, 2014
Messages
2
im using the vba to make a userform to help father in law manage data in his personal business. he is still learning to use this stuff so im trying to make it as easy as possible for him. I have found the code:
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.TextBox_name.Value) = "" Then
Me.TextBox_name.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox_number.Value
ws.Cells(iRow, 2).Value = Me.TextBox_name.Value
ws.Cells(iRow, 3).Value = Me.TextBox_date.Value
ws.Cells(iRow, 4).Value = Me.TextBox_owed.Value
ws.Cells(iRow, 5).Value = Me.TextBox_paid.Value
MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.TextBox_number.Value = ""
Me.TextBox_name.Value = ""
Me.TextBox_owed.Value = ""
Me.TextBox_paid.Value = ""
Me.TextBox_name.SetFocus
End Sub

that adds data to the next line and works good. but his list is what people owe him, so say a person pays that is like 2nd on list of 10 people, he will delete that person and there is a blank space in the list. the code will not see that space and will add it to the bottom. I would like to get the code to fill in any line open first to keep the list organized and compact. any help will be greatly apperciated
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
This returns the row of the 1st blank cell in column A

Code:
iRow = ws.Range("A:A").Find(What:="", SearchOrder:=xlByRows, SearchDirection:=xlNext, LookIn:=xlValues).Row
 

apocalypticdon

New Member
Joined
Oct 4, 2014
Messages
2
This returns the row of the 1st blank cell in column A

Code:
iRow = ws.Range("A:A").Find(What:="", SearchOrder:=xlByRows, SearchDirection:=xlNext, LookIn:=xlValues).Row

thank you so much. I knew it couldn't be too hard but I been looking at several places and never seen how to do it. people always just find last row of table or whatever and never a empty spot. thanks for quick response
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,278
You're welcome.

Alternatively; before adding the data, sort the rows (maybe based on the date column). That would remove blank rows in between. Then add the data to the bottom as before. That way, the rows are chronological. Just a thought.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,353
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top