Delete a Row in a Table

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
99
Office Version
  1. 365
Platform
  1. Windows
Good morning Experts

I have a UserForm which works but needs adjustment

The original Userform [below]

Original UserForm.png


would look for empty cells in a sheet and appear when it found any in either of 2 columns. When it activated, it showed the current content of the Date and Description columns and the Value [which they do not change] and the User is then asked to choose a Header and a VAT Type.

It has become apparent that there may be times where the row does not need to be present and the User may wish to Delete the Row

New Userform.png


This where my problem is because the current code [below] does not actually select the row and I need it to do this so I can allow the User to Delete the Row if required

Can anyone help with code to make it select a cell in the row with the empty cell and delete the row for the Delete Row button??

Many thanks

Code:
Current UserForm Code

Option Explicit
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdCommit_Click()
If Me.cmbHeaders.Value = "" Or Me.cmbVATType = "" Then
MsgBox "You must complete all missing fields."
Exit Sub
End If
currentHeader = Me.cmbHeaders.Value
currentVATType = Me.cmbVATType.Value
Unload Me
End Sub
Private Sub UserForm_Initialize()
currentHeader = ""
currentVATType = ""
' HideTitleBar Me
End Sub

Current Sheet Code

Sub Find_Empty_Cells()
Application.ScreenUpdating = False
Dim lastRow As Long, rng As Range, response As String, strMissingData As String, strExistingData As String
lastRow = Sheets("Period Bank Statement").Range("A9999").End(xlUp).Row
For Each rng In Range("A2:E" & lastRow)
If Len(rng) = 0 Then
With frmMissingData
.lblDateValue = rng.Offset(0, 1 - rng.Column).Value
.lblDescValue = rng.Offset(0, 2 - rng.Column).Value
.lblAmountValue = Format(rng.Offset(0, 5 - rng.Column).Value, "£#,##0.00")
.Show
End With
Select Case rng.Column
Case 6
rng = currentHeader
rng.Offset(0, 1) = currentVATType
Case 7
rng = currentVATType
End Select
End If
Next rng
Application.ScreenUpdating = True
End Sub
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,044
Office Version
  1. 2010
Platform
  1. Windows
Add a textbox to the user form, can be hidden, and populate it with rng.row in the
With frmMissingData portion of the code then you'll know what row to deal with.
 

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
99
Office Version
  1. 365
Platform
  1. Windows
Tried that and maybe I have the code wrong

Did this:

Private Sub lblDateValue_Change()
textbox1.value = rng.Row
End Sub

Textbox shows no value

Any ideas?
 

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
99
Office Version
  1. 365
Platform
  1. Windows
OK I now have the row number in the Textbox
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,044
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

That's not the With frmMissingData portion of the code.
Rich (BB code):
With frmMissingData
.textbox1.value = rng.Row
.lblDateValue = rng.Offset(0, 1 - rng.Column).Value
.lblDescValue = rng.Offset(0, 2 - rng.Column).Value
.lblAmountValue = Format(rng.Offset(0, 5 - rng.Column).Value, "£#,##0.00")
.Show
End With
 

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
99
Office Version
  1. 365
Platform
  1. Windows
Thanks - I did eventually work that out
My issue now is creating the code to delete the row that is noted in the TextBox
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,044
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

You mean like this ?
Sheets("Sheet1").Rows(Me.TextBox1.Value).Delete
 

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
99
Office Version
  1. 365
Platform
  1. Windows
I'm really sorry to keep coming back to you but:
  1. with your direction I have got it to delete the row - thank you
  2. but now the code throws up an error in Module code at Select Case rng.Column as it errors with Object Required
Any ideas?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,044
Office Version
  1. 2010
Platform
  1. Windows
Sorry, if that code was OK before I don't know why it wouldn't be now.
If you can share a sample workbook with this issue I'll be happy to investigate.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,913
Members
414,110
Latest member
docops

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