Someone emailed me this week, about a problem he was having with my sample Part Data Entry UserForm.
When I took a look at the workbook, everything seemed okay, and the code had been copied and altered correctly.
Excel Named Table
Then I noticed that there was a formatted Excel table on the data collection sheet, which wasn’t in my original file.
That can cause problems if you’re using Excel VBA to add data to the first blank row on the worksheet.
Change the Last Row Code
In the comments for my Find First Blank Row blog post, Rick Rothstein suggested this code revision:
LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row
Rick mentioned that this formula ignores cells with formulas that are displaying the empty string. If your situation is such that you need to identify formula cells that might be displaying the empty string, then change the xlValues argument to xlFormulas.
Revised Excel VBA Code
So, I changed the Part Data Entry code, to use the Find method for finding the last row. I replaced this old line of code:
'find first empty row in database iRow = ws.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row
With this line of code:
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
Parts Data Entry UserForm With Combo Boxes
There is another version of the Parts Data Entry UserForm, and it’s a little fancier, with combo boxes to select parts and locations.
I’ve updated the Parts Data Entry UserForm With Combo Boxes too, with the revised last row code.
Get the Updated Sample Files
You can download the updated versions of the parts data entry forms on the Contextures website.
Parts Data Entry UserForm With Combo Boxes
_________________
Your tutorial on how to create a UserForm in Excel was great!! This is a great way to add new data, but what if we want to update data already in the spreadsheet/database? Do you have a tutorial for that? Thanks so much!! David
@David, there is a worksheet data entry form in which you can update the existing data:
http://www.contextures.com/exceldataentryupdateform.html
I haven’t done a UserForm version with that feature yet, and thanks for the suggestion.
Dear Sir,
I went to your site it is nice and got idea how to add an record is it possible you to help on the below topics.
1.By entiring the more than two data’s in a each colum able to select the total data
2.Need help in the excel vba macros pls send the smaple form to follow.
Awaiting your reply
regards.
Abrar
Dear Sir,
Instead of saving data in the same file, I created another one for database purpose only. Using your sample as the main form (PartLocDB.xls), how can I add (instead of the same worksheet)the data directly to other file like (PartLocDB2.xls)as main database.
Below is the parameters that need adjustment
Set ws = Worksheets(“PartsData”)
No matter what VBA code I use I cannot get the second line of information (entered from a UserForm) copied into the second line of a Table.
The first line of information from the UserForm copies just fine into the first line of the Table. It’s the second line that is giving me fits.
The Code is…Thanks Debra for any suggestions – I’m about Googled Out – smile.
Private Sub cmdbtnSave_Click()
Dim NewRowOfData As Long
Dim ws As Worksheet
Set ws = Worksheets(“WasteWaterUsage”)
Set rRng = ws.Range(“B6″)
‘Test to see if 1st cell in row is empty
If IsEmpty(rRng.Value) Then
NewRowOfData = 6
Else
‘NewRowOfData = ws.Cells(Row.Count, 1).End(xlUp).Offset(1, 0).Row
NewRowOfData = ws.Cells.Find(What:=”*”, SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
End If
‘Input data from UserForm to Worksheet
With ws
.Cells(NewRowOfData, 2).Value = Me.txtbStartDate.Value
.Cells(NewRowOfData, 3).Value = Me.txtbEndDate.Value
.Cells(NewRowOfData, 8).Value = Me.txtbGallons.Value
.Cells(NewRowOfData, 5).Value = Me.txtbWeekends.Value
.Cells(NewRowOfData, 6).Value = Me.txtbIdleDays.Value
‘.Cells(NewRowOfData, 2).Activate – this was in another workbook where data input works fine – here gives an error
End With
‘Clear Input Values
Me.txtbStartDate.Value = “”
Me.txtbEndDate.Value = “”
Me.txtbGallons.Value = “”
Me.txtbWeekends.Value = “”
Me.txtbIdleDays.Value = “”
End Sub
Data begins in column B row 6 but actually covers 9 columns,
the Table itself covers Columns A through I, A is calculated, therefore actual input begins in column B.
Best wishes, Mort
@Mort, I’m not clear on what you mean by a second row of data from the User Form.
Your code is finding the first blank row, and putting all the values in that row.
Should something be going into the row below that?
Thanks Debra so much – and I get soooo frustrated with the way folks write and seems I’m one of’em – smile.
Have a Table – will have 6 rows of data for 6 months of information, and about 6-7-8 columns of data per row.
At the end of the 6 months, the Table will be cleared – have that code, and the formulas will be maintained.
Beginning with the next 6 months, month 1, user will enter information via a UserForm and that info should be copied to the Table by cmdSave button on UserForm.
I then need to find the next empty row for which month 2’s data will be inserted. While I try to get month 2 inserted, it never works….now row 1 is not working.
My Table has headers by the way….I’ve copied I don’t know how many codes and tried them and there are errors with every single one of them – NONE WORK.
Col A is calculated so entry begins with column B – because there is a “leg” of calculations hanging below the table on the right of it – like a fly lying “L”
I think I need to do the next empty row specific to column B?
My brain cells hurt to day – thanks…
Mort, Dallas
I was correct – the “L” shape is causing the issues, I have to find a way to search for next empty row via column B.
Code works but is seeing wrong place
NewRowOfData = ws.Cells.Find(What:=”*”, SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
But is finding the row under the lower part of the L in the data range – If I could post a graphic you’d see right away need to search a specific column, like B
The important lesson to be learned here…..regarding adding a new “empty” row to a TABLE
If there is information below that TABLE the normal method to find the next empty row of the TABLE does not work!!
The xlup or whatever, due to the fact there is info below the TABLE
The solution….taaaa daaaaaa
Work on the TABLE itself
In abbreviated form insure you include this in your code:
Dim ws as Worksheet
Dim tbl As ListObject
Set tbl = ws.ListObjects (“name of your table here”)
tbl.ListRows.Add
Set LastRow = tbl.ListRows(tbl.ListRows.Count).Range
now enter your data from your UserForm
Also to note: Only information directly under your TABLE will be moved down as new rows are added.
If you have information not under the TABLE, that information will stay put.
I had to add extra blank columns to my TABLE to account for information under the TABLE that
extended beyond the end of the TABLE.
Hope this is of some help?
Thanks Mort!
When I use the
iRow = ws.Cells.Find(What:=”*”, SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
it goes to the end of my table some 25 rows past where I would like it to start.
How do I get it to start, for example at A8 and look down from there to find the next empty row?
If I have SUM= in some of these will that cause for a full row?
Debra
Many thanks for the guidance on https://www.contextures.com/xlForm02.html and other pages.
I love the userform that I have created for data input / update into a large table.
A question – userform and associated navigation buttons will work sequentially through each row of the data table.
If I add filters to the data table – is there any way for the userform to only view the filtered results and the navigation buttons would move me though the filtered rows, not all rows?
Many thanks
Tim, you could use SUBTOTAL or AGGREGATE to number the filtered items, then use those numbers to move through the records.
These pages show examples of that type of numbering:
https://www.contextures.com/exceldropdownfilteredlist.html
https://www.contextures.com/excelfunctionsubtotal.html
https://contexturesblog.com/archives/2013/05/21/sum-a-filtered-list-with-aggregate-function/