| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete cells if
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete cells if
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 --------------- Jacob Skaria "LiAD" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete cells if
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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "LiAD" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete cells if
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 --------------- Jacob Skaria "LiAD" wrote: 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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "LiAD" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Delete cells if
Perfecto
Cheers "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "LiAD" wrote: 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 "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "LiAD" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| delete cells column. Delete empty cells | Excel Worksheet Functions | |||
| how do I merge cells into one then delete the original cells? | Excel Worksheet Functions | |||
| How to delete all the blanc cells in a worksheet and shift cells l | Excel Discussion (Misc queries) | |||
| macro to select cells containing specific text and delete all cells but these | Excel Programming | |||
| Delete specific cells contents in a row with some locked cells in the same row | Excel Programming | |||
Hybrid Mode
