Delete Entire Row

  • Thread starter Thread starter Corey
  • Start date Start date
C

Corey

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....
 
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)



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....
 
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....
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)



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....
 
Yes rng is type Range, iPos is type Long. But you have to setup rng, to the
same worksheet range that contains the data that you use to populate the
combobox, it doesn't exist of itself..

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Bob,
So correct me if i am wrong.
I have a :
Dim FindIT As Range At the top already
Dim iPos as Long <== Goes at the top of the routine as well

Dim rng as Range <== Goes where?
Do i place it in the Code for the SheetSelectionCode ?

Yes rng is type Range, iPos is type Long. But you have to setup rng, to the
same worksheet range that contains the data that you use to populate the
combobox, it doesn't exist of itself..

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top