Removing Valsues From A Range

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,812
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:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

iyyi

Active Member
Joined
Jun 5, 2012
Messages
353
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
 

Ark68

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

Ark68

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

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

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
Top