Delete a Row in a Table

tigerdel

Board Regular
Joined
Oct 13, 2015
Messages
145
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:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Thanks - I did eventually work that out
My issue now is creating the code to delete the row that is noted in the TextBox
 
Upvote 0
You mean like this ?
Sheets("Sheet1").Rows(Me.TextBox1.Value).Delete
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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
Back
Top