Match Code

[Todd Huttenstine]

Hey guys,

On a userform I have Combobox4. On worksheets(8) Range
J3:J100 I have a range of values. Range K3:S100 is a
range of cooresponding values. The value in Combobox4
will match a value in range J3:J100. When it finds this
match in this range, I need for it to delete the values in
the matching row from column J to column S. I then need
for the code to pull all value below the deleted values up
to fill in the gap caused by the deletion.

For example I have data in range J3:S27. The value of
Combobox4 is "Dog" The value "Dog" is found in cell J23.
The code should then delete cells J23:S23 and then pull
the values in the remaining cells in columns J through S
of rows 24, 25, 26, and 27 up to fill in the gap when
cells J23:S23 were deleted.


Thank you

Todd Huttenstine

 

[Dick Kusleika]

Todd

Dim FndRng as Range

Set FndRng = Worksheets(8).Range("J3:j100").Find(Me.Combobox4.Value)

If Not FndRng Is Nothing Then
FndRng.EntireRow.Delete
End If

Assuming you can delete the whole row without messing up any other data.
Also, it's better to specify all the arguments for the Find method, although
I didn't in this example.

 

[Todd Huttenstine]

I cant delete the entire row because it will mess up more
data. How I delete only the cells I specified?

 

[Dick Kusleika]

Todd

Instead of

use

FndRng.Resize(,10).Delete xlShiftUp

 

[Todd Huttenstine]

Thank you

Will try this out and post back.

-----Original Message-----
Todd

Instead of


use

FndRng.Resize(,10).Delete xlShiftUp

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.




.