Good morning Experts
I have a UserForm which works but needs adjustment
The original Userform [below]
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
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
I have a UserForm which works but needs adjustment
The original Userform [below]
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
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: