Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete entire row if | Excel Discussion (Misc queries) | |||
Need to delete last value in column but not delete entire row | Excel Programming | |||
Delete Entire Row | Excel Programming | |||
Delete Entire Row If Q | Excel Programming | |||
Delete Entire Row. | Excel Programming |