Clear Contents Between Two Values

[eduardoricardo52]

Hi Guys,

Bit of a newbie here.

I'm working on a very large set of data. Each column contains over 20000 values and I've got a bit of a dilemma.

I'm trying to create a macro that can find any values between 0.0401 and 0 AND any values between -0.036 and 0 and clear the contents as well as remove any blank cells.

I've been searching for a little bit and have been trying to get myself informed but I can't seem to find anything relating to this that I can understand!

Any help would be appreciated.

Thanks!

 

[AlexaS]

With the "remove any blank cells" portion of your post: are you looking to just shift that data up one space, or are you looking to delete the entire row?

 

[Le0nard0]

Just thinking. It might be easier to set conditional formatting on the entire sheet. Use it to highlight the cells you want deleted. Then write a macro that searches for the cells that are highlighted and deletes them.

If you need help with that just ask.

 

[eduardoricardo52]

With the "remove any blank cells" portion of your post: are you looking to just shift that data up one space, or are you looking to delete the entire row?

I was just thinking of moving the data up by one place, sorry for not specifying! Thanks for your reply!

Just thinking. It might be easier to set conditional formatting on the entire sheet. Use it to highlight the cells you want deleted. Then write a macro that searches for the cells that are highlighted and deletes them.

If you need help with that just ask.

I see what you mean with the conditional formatting, but I haven't the slightest inclination on how to go about writing that macro. Thanks!

 

[eduardoricardo52]

Bump.

 

[AlexaS]

I'm trying to create a macro that can find any values between 0.0401 and 0 AND any values between -0.036 and 0 and clear the contents as well as remove any blank cells.

You would want something along the lines of (loop code found and modified from here & delete code found here):

Sub test()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("B" & i).Value >=-0.036 and Range("B" & i).Value <=0.0401 Then Range("B" & i).Delete Shift:=xlUp
Next i
End Sub

You'll have to play with this to make sure it works the way you need it to. For example, if you aren't looking at a single column, you'll need to adjust for your selected range.

Also - you didn't need to bump. Most people respond on this forum during business hours. Getting a response after hours is kind of rare (and kind of awesome).