find a value and delete that row

[Guest]

I am looking for a macro for..... Find zero values in range H:H of this
w/sheet and delete those entire rows.If no cell in range H:H has zero value
do nothing.
say if cell H232=0 ,delete row number 232 ,and row 233 to end row will move
up in the w/sheet.

 

[Paul B]

Give this a try,

Sub Delete_blank()
Intersect(Range("H:H"), ActiveSheet.UsedRange) _
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

On Error Resume Next
Columns("H:H").Find(What:=0, After:=[H1], LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).EntireRow.Delete Shift:=xlUp
On Error GoTo 0

Regards,
Stefi


„TUNGANA KURMA RAJU†ezt írta:

 

[Guest]

Mr.Paul,this gave me a runtime error 1004.

Give this a try,

Sub Delete_blank()
Intersect(Range("H:H"), ActiveSheet.UsedRange) _
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Paul B]

Could be there are no blank cells, or the sheet is protected, try this

Sub Delete_blank()
On Error Resume Next
Intersect(Range("H:H"), ActiveSheet.UsedRange) _
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Dave Peterson]

But the OP is looking to delete rows that have 0 values in that column--not
empty cells.

 

[Guest]

This macro is only deleting one row (that has a zero value in H:H range)
from top. I want all the rows that have zero values in H:H range.

On Error Resume Next
Columns("H:H").Find(What:=0, After:=[H1], LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).EntireRow.Delete Shift:=xlUp
On Error GoTo 0

Regards,
Stefi


„TUNGANA KURMA RAJU†ezt írta:

I am looking for a macro for..... Find zero values in range H:H of this
w/sheet and delete those entire rows.If no cell in range H:H has zero value
do nothing.
say if cell H232=0 ,delete row number 232 ,and row 233 to end row will move
up in the w/sheet.

 

[Paul B]

Dave, one of them days, miss read the post, this should do it

Sub Delete_Zero_Rows()
Dim lastrow As Long, r As Long
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
For r = lastrow To 1 Step -1
If Cells(r, "H") = 0 Then
Rows(r).EntireRow.Delete
End If
Next r
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

I have not asked for blank rows.I want delete those rows that have 0(zero)
values in H:H range of w/sheet used range.

 

[Paul B]

Ok, think I need to just start the weekend NOW, put quotes around the 0

Sub Delete_Zero_Rows()
Dim lastrow As Long, r As Long
lastrow = Cells(Rows.Count, "H").End(xlUp).Row
For r = lastrow To 1 Step -1
If Cells(r, "H") = "0" Then
Rows(r).EntireRow.Delete
End If
Next r
End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Thanks,Paul B,it is now perfectly all right.Thanks for your help.

 

[Guest]

HI Paul,

I am not a experience user in Macro but I need this exact function in my
day-to-day work. I have pasted the function in Macro Module but it doesn't
work. Would it be possible that you explain it step-by-step i.e Alt F11
followed by... .... Really appreciate your help as it will reduce time spent
greatly.

Sharon
Singapore