find a value and delete that row
 

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.

find a value and delete that row
 

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

"TUNGANA KURMA RAJU" wrote in
message ...
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.

find a value and delete that row
 

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.

find a value and delete that row
 

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

"Paul B" wrote:

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

"TUNGANA KURMA RAJU" wrote in
message ...
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.

find a value and delete that row
 

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

"TUNGANA KURMA RAJU" wrote in
message ...
Mr.Paul,this gave me a runtime error 1004.

"Paul B" wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
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.

find a value and delete that row
 

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

Paul B wrote:

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

"TUNGANA KURMA RAJU" wrote in
message ...
Mr.Paul,this gave me a runtime error 1004.

"Paul B" wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
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.




--

Dave Peterson

find a value and delete that row
 

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.
"Stefi" wrote:

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.

find a value and delete that row
 

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

"Dave Peterson" wrote in message
...
But the OP is looking to delete rows that have 0 values in that
column--not
empty cells.

Paul B wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
Mr.Paul,this gave me a runtime error 1004.

"Paul B" wrote:

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

"TUNGANA KURMA RAJU"
wrote
in
message ...
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.




--

Dave Peterson

find a value and delete that row
 

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" wrote:

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

"TUNGANA KURMA RAJU" wrote in
message ...
Mr.Paul,this gave me a runtime error 1004.

"Paul B" wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
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.

find a value and delete that row
 

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


"TUNGANA KURMA RAJU" wrote in
message ...
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" wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
Mr.Paul,this gave me a runtime error 1004.

"Paul B" wrote:

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

"TUNGANA KURMA RAJU"
wrote
in
message ...
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.

find a value and delete that row
 

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

"Paul B" wrote:

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


"TUNGANA KURMA RAJU" wrote in
message ...
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" wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
Mr.Paul,this gave me a runtime error 1004.

"Paul B" wrote:

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

"TUNGANA KURMA RAJU"
wrote
in
message ...
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.

find a value and delete that row
 

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

"TUNGANA KURMA RAJU" wrote:

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

"Paul B" wrote:

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


"TUNGANA KURMA RAJU" wrote in
message ...
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" wrote:

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

"TUNGANA KURMA RAJU" wrote
in
message ...
Mr.Paul,this gave me a runtime error 1004.

"Paul B" wrote:

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

"TUNGANA KURMA RAJU"
wrote
in
message ...
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.