Removing Valsues From A Range

[Ark68]

Suppose I have a range of cells: T3:Tx (x is dynamic)

Somewhere in that range, a value exists, DRHPE for example. How can I find it in the list, delete it, and shift everything below it up?

I also have this line of code ...

wshvar.Range("T" & nextrow).Resize(6).Value = Application.Transpose(Array("WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT"))

which adds 6 values to the range in question. How can I remove these 6 values and shift up those remaining values below them up?

 

[iyyi]

You can run the following macro...

Sub RemoveVals()
x = 24 ' The last Row
For i = 3 To x
    Select Case Cells(i, 20).Value
        Case "WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT"
            Range(Cells(i, 20), Cells(x, 20)) = Range(Cells(i + 1, 20), Cells(x, 20)).Value
            Cells(x, 20) = ""
            x = x - 1
    End Select
Next i
End Sub

 

[Ark68]

Hi iyyi ...
Thank you for your suggestion. Unfortunately, it doesn't appear to be working.
As I step through the code...

Sub RemoveVals()

Dim x As Integer
Dim i As Integer

x = 24 ' The last Row
For i = 3 To x
    Select Case Cells(i, 20).Value
        Case "WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT"
            Range(Cells(i, 20), Cells(x, 20)) = Range(Cells(i + 1, 20), Cells(x, 20)).Value
            Cells(x, 20) = ""
            x = x - 1
    End Select
Next i
End Sub

The code jumps between the green lines, ignoring the red the red highlighted lines on all 24 loops.

Jenn

 

[Ark68]

I had to revise your original code to include the worksheet reference.

Sub RemoveVals()

Dim x As Integer
Dim i As Integer

x = 24 ' The last Row
For i = 3 To x
    Select Case Cells(i, 20).Value
        Case "WPEDR", "WPEDT", "WPEFR", "WPEFT", "WPECR", "WPECT"
            Worksheets("varhold").Range(Cells(i, 20), Cells(x, 20)) = Worksheets("varhold").Range(Cells(i + 1, 20), Cells(x, 20)).Value
            Worksheets("varhold").Cells(x, 20) = ""
            x = x - 1
    End Select
Next i

End Sub

In the holding range worksheets("varhold").Range("T3:T24"), the values CUEDR, HPLFR, WPEDR, WPEFR, WPEDT, WPEFT, WPECR and WPECT. When I run the code, the expectation is that the values in red will be deleted. What is happening though, is only valuyes WPEDR, WPEFR and WPECR are being deleted. The remaining 3 ... WPEDT, WPEFT and WPECT remain. CUEDR and HPLFR also remain, but that is expected.

 

[iyyi]

Can You upload and share the link to the actual data?