Delete cells if

L

LiAD

Morning,

Could some please give me a macro that will delete the contents of any cells
in the range A1 to F100 if the value in the corresponding F cell = 0. So the
table would look like

A B C D E F
xx yy aa pp 1
dd aa uu rr 1
oo kk rr ee 0
kk mm nn gg 0

and i would like the macro to delete A3:F4. All the the list will be a
continous string of 1's followed by a break to zero then a continous list of
zero's, it will not read 1 1 1 0 1 0 1 for example, only 1 1 1 1 0 0 0

Thanks
LiAD
 
J

Jacob Skaria

If you mean to delete the rows with ColF 0 try the below

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "F").End(xlUp).Row To 1 Step -1
If Range("F" & lngRow).Text = "0" Then Rows(lngRow).Delete
Next
End Sub

If this post helps click Yes
 
L

LiAD

Hi,

Sorry this is deleting the whole row.

I was looking for something that just deletes the contents of cells A to F.

Thanks again
 
J

Jacob Skaria

Try the below...

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "F").End(xlUp).Row To 1 Step -1
If Range("F" & lngRow).Text = "0" Then
'To clear contents
Range("A" & lngRow & ":F" & lngRow).ClearContents

'To shift the cells up without deleting the entire row
'Range("A" & lngRow & ":F" & lngRow).Delete Shift:=xlUp
End If
Next
End Sub


If this post helps click Yes
 
L

LiAD

Perfecto

Cheers

Jacob Skaria said:
Try the below...

Sub DeleteRows()
Dim lngRow As Long
For lngRow = Cells(Rows.Count, "F").End(xlUp).Row To 1 Step -1
If Range("F" & lngRow).Text = "0" Then
'To clear contents
Range("A" & lngRow & ":F" & lngRow).ClearContents

'To shift the cells up without deleting the entire row
'Range("A" & lngRow & ":F" & lngRow).Delete Shift:=xlUp
End If
Next
End Sub


If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top