Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|