VB Deleting partial row in table based on useform selection

[Daburger]

Hi All,

I wanted to learn more about VB and so I thought I'd try out making a data entry logger.
The Userform I made has 2 sections to it.
One for normal training logs and another for adhoc training logs with a list for each. They both work independantly to each other except exit and save.
Please see the image below:

From the Workbook, I have two tables where the data above is being displayed from.
They are both on the same sheet, side by side with a gap inbetween
Sheet name: Data
Table 1: Cells A-G
Table 2: Cells I-M

I created the above table starting with the left side first and I got it working flawlessly.
The issues started when I couldnt work out the code for deleting a row from the Adhoc Training ListBox (Data located on Table2 from the same sheet)

The code that I used when creating the first half was for deleting an entire row based on the ID No of the row I had selected. The 2 small boxes you can see in the middle of the userform are used to display the row I had selected after double clicking in either of the ListBoxes. These ID NO boxes are hidden as I had no reason to show them generally.

The real problem is that now I have 2 tables side by side on the sheet where the data lives, my form errors out when i go to delete a log entry.
I tried using the same data however that won't work as it relies on deleting the whole row.

My question is, using the same format below, how can I delete a row from cells A-G after selecting a particular ID No?
And the same question for Cells I-M after selecting a particular ID No?

Below is the code I have for both delete buttons:



Private Sub delete1_Click() - This is the delete Button 1 from the LEFT side

If Me.TxtBox2.Value = "" Then - this text box is hidden and will have the ID number of which ever row I select
MsgBox "Select Log to Delete"
Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TxtBox2.Value), sh.Range("A:A"), 0) - this text box (txtbox2) is hidden and will have the ID number of which ever row I select
'---------------------------------------------------------------
sh.Range("A" & Selected_Row).EntireRow.Delete
'-------------------------------------
Me.ComboBox1.Value = "" - these are the input fields on the left side of the userform
Me.ComboBox2.Value = ""
Me.ComboBox3.Value = ""
Me.ComboBox4.Value = ""
Me.ComboBox5.Value = ""
'---------------
Call Refresh_Data

End Sub
____________________________________________________________________________________________________

Private Sub delete2_Click() - This is the delete Button 2 from the RIGHT side

If Me.TxtBox3.Value = "" Then
MsgBox "Select Log to Delete"
Exit Sub
End If

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim Selected_Row As Long
Selected_Row = Application.WorksheetFunction.Match(CLng(Me.TxtBox3.Value), sh.Range("I:I"), 0) - this text box (txtbox2) is hidden and will have the ID number of which ever row I select
'---------------------------------------------------------------
sh.Range("I" & Selected_Row).EntireRow.Delete
'-------------------------------------
Me.ComboBox6.Value = "" - these are the input fields on the RIGHT side of the userform
Me.TxtBox1.Value = ""
Me.ComboBox7.Value = ""
'---------------
Call Refresh_Data

End Sub

 

[Crystalyzer]

Can't you simply put the Ad Hoc training table in another worksheet?

 

[AlphaFrog]

Try this syntax to delete a row from A:G

sh.Range("A" & Selected_Row & ":G" & Selected_Row).Delete xlShiftUp

 

[Daburger]

Can't you simply put the Ad Hoc training table in another worksheet?

I originally started with that, however I had other issues displaying that data from the 2nd sheet into the 2nd listbox. In the end, It is better to have them on the same sheet as it reduces the amount of sheets in my workbook

 

[Daburger]

Try this syntax to delete a row from A:G

sh.Range("A" & Selected_Row & ":G" & Selected_Row).Delete xlShiftUp

Hi AlphaFrog,
This was spot on! This worked right off the bat! Thank you so much for your help and time!
I just replicated the same code for the 2nd listbox but changed the cells from A to I and G to M

sh.Range("I" & Selected_Row & ":M" & Selected_Row).Delete xlShiftUp