Delete Entire Row
Bob,
Do i need a :
Dim rng as Range and something for the iPos ?
I get a error.
If i put a 'On error rersume next' there is NO deleted Row?
Private Sub CommandButton1_Click()
Dim FindIT As Range
' Dim rng As Range
Application.ScreenUpdating = False
With Sheets("Sheet1")
' On Error Resume Next
Set FindIT = .Range("a:a").Find(What:=DateValue(ComboBox1.Value ))
FindIT.Offset(0, 1).Value = ComboBox3.Value
FindIT.Offset(0, 2).Value = Val(ComboBox2.Value)
FindIT.Offset(0, 3).Value = ComboBox2.Column(0)
End With
Sheets("Sheet2").Select
' Add Delete entire Row code here
iPos = Application.Match(ComboBox2.Value, rng, 0)
rng.Cells(iPos, 1).EntireRow.Delete
With ComboBox2.Value
ComboBox2.Copy
End With
Sheets("Sheet2").Select
Columns("A:A").Select
Selection.Find(What:=ComboBox2.Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Rows.Select
Application.CutCopyMode = False
Range("A1").Select
Sheets("Sheet1").Select
Unload Me
Application.ScreenUpdating = True
End Sub
Corey....
"Bob Phillips" wrote in message ...
Find the index of the matching item
iPos = Application.Match(Combobox2.value,rng,0)
where rng points at the range of data in the combo.and delete it with
rng.Cells(iPos,1).Entirerow.delete
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Corey" wrote in message ...
Need 1 fine adjustment I left out, that I had working before, but cannot now.
To ADD a Delete ROW in code below where value Combobox2 came from, to delete the row that the values
came from to prevent them being used again.
Code:
Private Sub CommandButton1_Click()
Dim FindIT As Range
Application.ScreenUpdating = False
With Sheets("Sheet2")
Set FindIT = .Range("a:a").Find(What:=DateValue(ComboBox1.Value ))
FindIT.Offset(0, 1).Value = ComboBox3.Value
FindIT.Offset(0, 2).Value = Val(ComboBox2.Value)
FindIT.Offset(0, 3).Value = ComboBox2.Column(0)
End With
Sheets("Sheet2").Select
' <==== Delete Combobox2.value Entire Row ???
Range("A1").Select
Sheets("Sheet1").Select
Application.ScreenUpdating = True
End Sub
Regards
Corey....
|