VB Deleting partial row in table based on useform selection

Daburger

New Member
Joined
Jul 15, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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:
1626663901975.png

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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Can't you simply put the Ad Hoc training table in another worksheet?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,434
Try this syntax to delete a row from A:G
VBA Code:
sh.Range("A" & Selected_Row & ":G" & Selected_Row).Delete xlShiftUp
 
Solution

Daburger

New Member
Joined
Jul 15, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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 :p
 

Daburger

New Member
Joined
Jul 15, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Try this syntax to delete a row from A:G
VBA Code:
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
VBA Code:
sh.Range("I" & Selected_Row & ":M" & Selected_Row).Delete xlShiftUp
 

Forum statistics

Threads
1,141,073
Messages
5,704,138
Members
421,328
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top