Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a column based on a value
Hey guys
On Sheet1, I have Range A4:Z4. There are names in some of these cells in this range. I have a userform that contains combobox1 which contains all the values in this range. I also have CheckBox4 and CommandButton1. I need to know the code that if checkbox1 is checked, will look in Range A4:Z4 on sheet1, and if the value of combobox1 matches a value in this range will delete that cell plus all the cells down until the 100th row. The way I need it to delete the row is by not just deleting the values in the cells, but to delete the cells and then shift the remaining cells over to the left. Thank you Todd Huttenstine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a column based on a value
So you want the trigger to be checkbox1 (not checkbox 4?) being
checked? Can there be more than one instance of the name in A4:Z4? If so, how should that be handled? Assuming only one instance: Private Sub CheckBox1_Change() Dim found As Range If CheckBox1.Value Then Set found = Sheets("Sheet1").Range("A4:Z4").Find( _ What:=ComboBox1.Value, _ After:=Sheets("Sheet1").Range("Z4"), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If Not found Is Nothing Then _ found.Resize(97, 1).Delete shift:=xlToLeft End If End Sub In article , "Todd Huttenstine" wrote: Hey guys On Sheet1, I have Range A4:Z4. There are names in some of these cells in this range. I have a userform that contains combobox1 which contains all the values in this range. I also have CheckBox4 and CommandButton1. I need to know the code that if checkbox1 is checked, will look in Range A4:Z4 on sheet1, and if the value of combobox1 matches a value in this range will delete that cell plus all the cells down until the 100th row. The way I need it to delete the row is by not just deleting the values in the cells, but to delete the cells and then shift the remaining cells over to the left. Thank you Todd Huttenstine |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a column based on a value
Sorry for my mistake. I meant checkbox4. I put the code
in the commandbutton click event. Here is the code... Private Sub CommandButton2_Click() Dim found As Range If CheckBox4.Value Then Set found = Sheets("Team Data").Range ("A4:Z4").Find( _ What:=ComboBox1.Value, _ After:=Sheets("Sheet1").Range("Z4"), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If Not found Is Nothing Then _ found.Resize(97, 1).Delete Shift:=xlToLeft End If End Sub I am getting an out of range error when run. -----Original Message----- So you want the trigger to be checkbox1 (not checkbox 4?) being checked? Can there be more than one instance of the name in A4:Z4? If so, how should that be handled? Assuming only one instance: Private Sub CheckBox1_Change() Dim found As Range If CheckBox1.Value Then Set found = Sheets("Sheet1").Range ("A4:Z4").Find( _ What:=ComboBox1.Value, _ After:=Sheets("Sheet1").Range("Z4"), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If Not found Is Nothing Then _ found.Resize(97, 1).Delete shift:=xlToLeft End If End Sub In article , "Todd Huttenstine" wrote: Hey guys On Sheet1, I have Range A4:Z4. There are names in some of these cells in this range. I have a userform that contains combobox1 which contains all the values in this range. I also have CheckBox4 and CommandButton1. I need to know the code that if checkbox1 is checked, will look in Range A4:Z4 on sheet1, and if the value of combobox1 matches a value in this range will delete that cell plus all the cells down until the 100th row. The way I need it to delete the row is by not just deleting the values in the cells, but to delete the cells and then shift the remaining cells over to the left. Thank you Todd Huttenstine . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting a column based on a value
hey actually I got it. I had to change ("sheet1") to the
name of my sheet. Sorry for the post. I guess I should look a little closer next time. Thank you. That code works great. Todd -----Original Message----- So you want the trigger to be checkbox1 (not checkbox 4?) being checked? Can there be more than one instance of the name in A4:Z4? If so, how should that be handled? Assuming only one instance: Private Sub CheckBox1_Change() Dim found As Range If CheckBox1.Value Then Set found = Sheets("Sheet1").Range ("A4:Z4").Find( _ What:=ComboBox1.Value, _ After:=Sheets("Sheet1").Range("Z4"), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If Not found Is Nothing Then _ found.Resize(97, 1).Delete shift:=xlToLeft End If End Sub In article , "Todd Huttenstine" wrote: Hey guys On Sheet1, I have Range A4:Z4. There are names in some of these cells in this range. I have a userform that contains combobox1 which contains all the values in this range. I also have CheckBox4 and CommandButton1. I need to know the code that if checkbox1 is checked, will look in Range A4:Z4 on sheet1, and if the value of combobox1 matches a value in this range will delete that cell plus all the cells down until the 100th row. The way I need it to delete the row is by not just deleting the values in the cells, but to delete the cells and then shift the remaining cells over to the left. Thank you Todd Huttenstine . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Rows based on a Value | Excel Worksheet Functions | |||
Deleting rows based on column values | New Users to Excel | |||
Deleting Rows based on Column Critieria | Excel Discussion (Misc queries) | |||
Deleting rows based on IF statement | Excel Discussion (Misc queries) | |||
Deleting rows based on a cell value | Excel Worksheet Functions |