how to Delete Data Row with use of VBA code

[rrosa1]

hi
i have two worksheet and one user form my code is working accepted deleting the row from worksheet "b" can somebody help me ?it give the error msg as
Run time error 438 object doesn't support property or method
it's newbie so pl be patience.


Private Sub Enterdata_Click()
Dim n As Long
Dim ws As Worksheet
Dim R As Range
Dim var
Dim i&


With Worksheets("B")
n = Application.Match((Me.CombT.Value), .Range("A:A"), 0)

.Cells(n, 4).Value = Me.noofs.Text

End With
'check for a Ticker
If Trim(Me.CombT.Value) = "" Then
Me.CombT.SetFocus
MsgBox "Please enter a T"
Exit Sub
End If
'correspondence with "T" and "#of S"
Set ws = Worksheets("H")

For i = 2 To ws.Cells(Rows.Count, 1).End(xlUp).Row
If Me.CombT.Value = ws.Cells(i, 1).Value Then
If MsgBox("T already exists in records. Do you still want to add the data to the records?", vbYesNo) = vbNo Then
Exit Sub
Else
Exit For
End If
End If
Next i


'Find the next empty row to copy the data to
Set Rng = ws.Range("A2")
Set RngEnd = ws.Cells(Rows.Count, Rng.Column).End(xlUp)
NextRow = IIf(RngEnd.Row < Rng.Row, Rng.Row, RngEnd.Row + 1)

With Worksheets("B")
If Trim(.Cells(n, "A")) = Trim(CombT.Value) And _
Trim(.Cells(n, "D")) = Trim(noofs.Value) Then

'copy the data to the database
With ws
.Cells(NextRow, "A").Resize(1, 10) = Worksheets("B").Cells(n, "A").Resize(1, 10).Value
.Cells(NextRow, "K").Value = Me.TextBox1.Value
.Cells(NextRow, "L").Value = Me.txtHoD.Value
.Cells(NextRow, "M").Value = Me.txtLoD.Value
.Cells(NextRow, "N").Value = Me.txtExitP.Value
.Cells(NextRow, "O").Value = Me.txtcom.Value
End With

End If

End With

Dim LR As Long
With Worksheets("B")
LR = Application.Match((Me.CombT.Value), .Range("A:A"), 0)
.EntireRow.Delete

End With


'clear the data
Me.CombT.Value = ""
Me.TextBox1.Value = Format(Date, "Medium Date")
Me.txtExitP.Value = ""
Me.txtcom = 7
Me.txtHoD.Value = ""
Me.txtLoD.Value = ""
Me.CombT.SetFocus

End Sub

Private Sub CombT_Change()
Dim vreg As String
Dim drow As Integer
Dim c As Range


If bEdit Then Exit Sub

vreg = CombT.Value

With Sheets("B").Range("Ticker")
Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then drow = c.Row
End With

With Me
.noofs.Value = Sheets("B").Cells(drow, 4).Value

End With





End Sub

[mdmackillop]

Welcome to VBAX



Dim LR As Long
With Worksheets("B")
LR = Application.Match((Me.CombT.Value), .Range("A:A"), 0)
.EntireRow.Delete

End With



You are not defining a row to delete

It should be something like


Dim LR As Long
With Worksheets("B")
LR = Application.Match((Me.CombT.Value), .Range("A:A"), 0)
.Cells(LR,1).EntireRow.Delete

End With

[rrosa1]

thanks for quick response it works but now it give the error msg in next sub

Run Time error 1004 Application define or object define error in


Private Sub CombT_Change()
Dim vreg As String
Dim drow As Integer
Dim c As Range


If bEdit Then Exit Sub

vreg = CombT.Value

With Sheets("B").Range("Ticker")
Set c = .Find(vreg, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then drow = c.Row
End With

With Me
.noofs.Value = Sheets("B").Cells(drow, 4).Value

End With

[mdmackillop]

Are you using Option Explicit. If not, add it at the top of the module and fix any highlighted problems.

[rrosa1]

Are you using Option Explicit. If not, add it at the top of the module and fix any highlighted problems.
thanks for help it's name defining need to fix i did that and it work great
thanks MD for help