delete row enside a range under condition on a cell

[asafraz81]

hi,

i want to write cod that make 2 things:

1. running on a range and check the cell value (i know how to do it)

2. if the cell is 0 or a number i define' then delete the row.


thanks for help its must be very simple

asaf

 

[shrivallabha]

Hi Asaf,


Try to write the code by yourself and in case of difficulty, raise your hand. It will give you more confidence.

 

[asafraz81]

i know how to write half of it, what i dont know is how to tell the code to go to cell number something and delete the row.

here is what i can write:

sub checkrow

dim cell as range

for each cell in range(somerange)

if cell.value="0" then

"here i need to write the code to delete the row

end if

next cell

end sub

plaese help me to close the code

 

[shrivallabha]

Try it like this:

[pre]

Sub CheckAndDeleteRow()
Dim cell As Range, rngDelete As Range

For Each cell In Range(somerange)
If cell.Value = "0" Then
If rngDelete Is Nothing Then
'At first instance rngdelete will have nothing
Set rngDelete = cell
Else
'2nd instance onwards we keep combining the ranges using union
Set rngDelete = Union(rngDelete, cell)
End If
End If
Next cell

'If we have something in rngdelete then we delete it using code below
If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete
End Sub

[/pre]

 

[Marc L]

Hi !     "here i need to write the code to delete the row" ->

Cell.EntireRow.Delete

You could found this code by using the macro recorder …

 

[asafraz81]

thanks.