ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Delete Entire Row (https://www.excelbanter.com/excel-programming/382622-delete-entire-row.html)

Corey
Delete Entire Row
 
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.Valu e))
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 Phillips
Delete Entire Row
 
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.Valu e))
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....

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



Bob Phillips
Delete Entire Row
 
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)



"Corey" wrote in message
...
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....

Corey
Delete Entire Row
 
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 ?

"Bob Phillips" wrote in message ...
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)



"Corey" wrote in message
...
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....


All times are GMT +1. The time now is 06:05 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com