Userform needs to ignore blank values when adding to table. Searching for faster coding

[SenatorBlairCheez]

Hey, all. New to the forum and really appreciate what you all have here. I've looked around the internet and several other forums for a way to increase the time it takes my code to add data to tables.

I have an excel database that stores all the data for projects that I've worked on and will work on and I prefer to add the data via a userform. The form functions well and adds, updates, deletes, etc. data just fine. However, it takes ~18s to add new projects to the database. I want to cut this time down if I can and I'm looking for help from the community here.

There are 39 total textboxes/comboboxes on the user form that can be filled out. For any given project, some and potentially most of these boxes will be blank either due to not having access to the information or not yet having the information due to the current status of the project.

Here's my code that adds jobs to the table...

Private Sub AddNewBtn_Click()
Set JL = Worksheets("JobLog")
Set Data = Worksheets("Data")
Dim daRow As Long
Dim dcRow As Long

'first EMPTY row
iRow = JL.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'MsgBox iRow

'identify first empty row in data table for approval phase
daRow = Data.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'MsgBox daRow

'identify first empty row in data table for construction phase
dcRow = Data.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).Row
'MsgBox dcRow


'check job search box for null or in-use job nums
If JobSearch.Value = "" Then
MsgBox "Please enter a new job number before proceeding"
Exit Sub
Else
CountJobs
If cnt <> 0 Then
MsgBox "Job number already exists. Please enter a NEW job number"
Else
AddJob

'copy the data to Data table
Data.Cells(daRow, 1).Value = JobSearch.Value
Data.Cells(daRow, 3).Value = "FA"
Data.Cells(dcRow, 1).Value = JobSearch.Value
Data.Cells(dcRow, 3).Value = "FC"
End If
End If
JLSortJobA
ClearBtn_Click

End Sub

"Add Job" is a subroutine that first defines where the column is based on the header row in another subroutine called "DefineCols" The code I use to define my column number for reference is

'Status
On Error Resume Next
StatCol = Application.WorksheetFunction.Match("Calc Status", JL.Range("joblabels"), 0)
On Error GoTo 0
If StatCol > 0 Then
End If



This is the first line and I do this line of code 38 more times for all the remaining textboxes/comboboxes. I won't post the entirety just to save space.

With the columns referenced and the first empty row identified by iRow I then add the values found in the textboxes/comboboxes using the AddJob subroutine. The code I use, again 39 times is as follows...

Status.Value = JL.Cells(sRow, StatCol)



So I believe that the process of going through each line of code individually is just taking a toll on the timing of the code. I've read about .screenupdating but I'm not exactly sure how I would employ this in the code. I've also read about storing data in a variant array but again this is something that I'm not very familiar with and with my limited knowledge may have a difficult time trying to incorporate that here.

Perhaps something more effective would be to simply ignore the blank values. Lastly, it is totally possible that I'm just going about this the wrong way entirely and I'd be open to suggestions on how I should have approached this from the beginning. If anyone has any advice or insight it would be greatly appreciated. Thanks!

 

[Roderick_E]

I wrap all my macros in this:

'Display wait for a moment
Application.StatusBar = "****Please Wait*****  Macro processing"
'opitmize macro by disabling all processes that slow it down.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False






'Re-enable screenupdating (before END SUB)
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.AskToUpdateLinks = true
Application.StatusBar = False