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

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

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
.

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
.