Deleting Rows Based on Value of a cell, HELP!!!!

[TD Rich]

Hello Excel People,

I have the following VBA code which i cant quite get to work as i want it to. What i am trying to achieve is for Excel to look at all rows that have an 'x' in column G. Then for those rows delete the cells in column A-E.

So.....

If row 5 had an 'x' in cell G5 then Excel will delete A5, B5, C5, D5 and E5.

Cheers.

TD Rich.

 

[TD Rich]

The code i currently have is as follows:

For i = 3000 To 1 Step -1
If Cells(i, "G") = "x" Then Range("A:E")(i).Delete
Next i
'
End Sub

this doesnt seems to work as i want it to though, any help would be greatly appreciated

 

[Jonmo1]

Try

If Cells(i, "G") = "x" Then Range("A" & i).Resize(1,5).Delete Shift:=xlup

 

[VoG]

Try

For i = 3000 To 1 Step -1
    If Cells(i, "G").Value = "x" Then Range("A" & i).Resize(,5).Delete shift:=xlShiftUp
Next i

 

[TD Rich]

VoG / Jonmo1

Cheers for that Guys, big help!!!!!!! Now i can go back to sleep!

Thanks,

TD Rich

 

[TD Rich]

Hello Jonmo1,

Got a follow up question on that. I have a formula in columns G & H which referance column A. When i delete column A using the coding you guys provided i am left with a #REF! referance in the cells containg the formula that referanced cloumn A. Is there anyway of removing the data in the cells but still allowing the formula in Columns G&H to referance column A?

Alternatively, is it possible to referance the relevent cells in columns G&H and rewrite the formula in as part of a macro?

Cheers,

TD Rich.

 

[TD Rich]

I am almost there code i have now is as follows:

For i = 3000 To 1 Step -1
If Cells(i, "G").Value = "x" Then Range("A" & i).Resize(, 5).Delete shift:=xlShiftUp
Range("E" & i).FormulaR1C1 = "=IF(RC[-4]>1,""yes"",""no"")"
Next i
End Sub


This does what i want but then copies the formula into every cell in column E. What i need is it to only copy the formula to the Cell in column E that is in the row that has had cells A- E deleted

 

[Jonmo1]

I am almost there code i have now is as follows:

This does what i want but then copies the formula into every cell in column E. What i need is it to only copy the formula to the Cell in column E that is in the row that has had cells A- E deleted

Just have to rearrange the IF a little bit

For i = 3000 To 1 Step -1
If Cells(i, "G").Value = "x" Then 
    Range("A" & i).Resize(, 5).Delete shift:=xlShiftUp
    Range("E" & i).FormulaR1C1 = "=IF(RC[-4]>1,""yes"",""no"")"
End If
Next i
End Sub

Hope that helps..

 

[TD Rich]

Thats perfect, Thanks Jonmo1!

have a good one!

Rich TD