Removing Valsues From A Range

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top