[RESOLVED] Delete Adjacent Cell Based on Value

[briancarlson]

Hello all,

I'm trying to perform a simple operation, this is how it looks logically:

Scan Range ("D17" & 17 + a)
IF[D17=""] Then Delete the adjacent cell... [Cell (0,1)] or [Range("E17").Delete]

I know the coding for this should be simple but I don't have any reference from a past project.

Thank you

[Fizziii]

Sub test()
Dim a as long
Dim myR as Range
Set myR = Range("D16")
For a = 1 to (whatever max you have for a)
    If myR.offset(a,0).value = ""
        'Delete code here
    End if
Next a
End Sub

I would record a macro of deleting a cell to make sure you have all the options for which way you want cells to shift.

[briancarlson]

Ok so I think I want to Clear.Contents instead of deleting the cell. Whats happening though is that the entire range is being selected instead of just the adjacent cell to the "" value.

Do I need to do something like:

If myR.Offset(a,0).value = "" Then Activate Adjacent cell and Clear.Contents

Check the next row down

Code Applied:

    Dim y As Long
    Dim myR As Range
    Set myR = Range("D16")
    For y = 1 To 250
        If myR.Offset(y, 0).Value = "" Then Selection.ClearContents
    Next y

[briancarlson]

I should probably give some more details about what I'm doing.

D1= 1
D2= ""
D3= 1
D4= 1
D5= ""

E1= Test
E2= Delete
E3= Test
E4= Test
E5= Delete

I need to clear out the contents wherever the "Delete" appears, however "Delete" is a random string. I cannot delete the entire row as the data is in a grid, once it works I'm going to replicate the formula then apply it to about half a dozen different fields.

[Fizziii]

I would change

If myR.Offset(y, 0).Value = "" Then Selection.ClearContents

to:

If myR.Offset(y, 0).Value = "" Then myR.Offset(y,1).ClearContents

[briancarlson]

Thanks! It works!

[briancarlson]

For use with Multiple ranges

    Dim y As Long
    Dim myD As Range
    Set myD = Range("D16")
    For y = 1 To 25
        If myD.Offset(y, 0).Value = "" Then myD.Offset(y, 1).ClearContents
    Next y
    
    Dim myG As Range
    Set myG = Range("G16")
    For y = 1 To 25
        If myG.Offset(y, 0).Value = "" Then myG.Offset(y, 1).ClearContents
    Next y
    
    Dim myJ As Range
    Set myJ = Range("J16")
    For y = 1 To 25
        If myJ.Offset(y, 0).Value = "" Then myJ.Offset(y, 1).ClearContents
    Next y

[Fizziii]

Could also do it as

Dim y as Long, z as Long
Dim myD as Range
Set myD=Range("D16")
For z = 0 to 2
    For y = 1 to 25
        If myD.Offset(y,z*3).Value = "" Then myD.Offset(y,(Z*3)+1).ClearContents
    Next y
Next z

(This works because D-G and G-J are 3 columns apart).

or

Dim y As Long
    Dim myD As Range
    Set myD = Range("D16")
    Dim myG As Range
    Set myG = Range("G16")
    Dim myJ As Range
    Set myJ = Range("J16")
    For y = 1 To 25
        If myD.Offset(y, 0).Value = "" Then myD.Offset(y, 1).ClearContents
        If myG.Offset(y, 0).Value = "" Then myG.Offset(y, 1).ClearContents
        If myJ.Offset(y, 0).Value = "" Then myJ.Offset(y, 1).ClearContents
    Next y

This just dumps all the Ifs into a single y For-Next run.