# Removing Valsues From A Range

#### Ark68

##### Well-known Member
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 ...
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?

Last edited:

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You can run the following macro...
Code:
``````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``````

Hi iyyi ...
Thank you for your suggestion. Unfortunately, it doesn't appear to be working.
As I step through the code...
Rich (BB 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

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

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"
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.

Replies
4
Views
109
Replies
1
Views
206
Replies
4
Views
322
Replies
5
Views
273
Replies
0
Views
147

1,214,749
Messages
6,121,305
Members
449,022
Latest member
benmerryman

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back