Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row***
Hi Mahesh, It is deleting entire row wherever zero appearing. But i required in a partiuclar column if Zero value found we need to delete entire row. Ex. If we use base as P column, In 13th row of P column contains 0 value it has to delete entire 13th Row. It should not search other columns. Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
RE: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row***
Hi Mahesh, Find below coding to delete the contains 0 value from 13th row of column P. Sub Zero_data() Dim Firstrow As Long Dim Lastrow As Long Dim Lrow As Long Dim CalcMode As Long Dim ViewMode As Long Dim rng As Range With Application CalcMode = .Calculation .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet .Select ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView .DisplayPageBreaks = False Firstrow = .UsedRange.Cells(13).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For Lrow = Lastrow To Firstrow Step -1 With .Cells(Lrow, P) If Not IsError(.Value) Then If .Value = 0 Then If rng Is Nothing Then Set rng = .Cells Else Set rng = Application.Union(rng, .Cells) End If End If End If End With Next Lrow End With If Not rng Is Nothing Then rng.EntireRow.Delete ActiveWindow.View = ViewMode With Application .ScreenUpdating = True .Calculation = CalcMode End With End Sub Regards, Shrinivas From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Prabhu Sent: Thursday, August 11, 2011 9:45 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row*** Hi Mahesh, It is deleting entire row wherever zero appearing. But i required in a partiuclar column if Zero value found we need to delete entire row. Ex. If we use base as P column, In 13th row of P column contains 0 value it has to delete entire 13th Row. It should not search other columns. Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row***
or just a little bit neater Sub MtestSAS() Application.ScreenUpdating = False For i =cells(rows.count,”P”).end(xlup).row To 1 Step -1 If Cells(i, 16).Value = 0 Then rows(i).Delete Next i Application.ScreenUpdating = True End Sub From: Mahesh parab Sent: Thursday, August 11, 2011 1:15 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row*** Hi Prabhu Try Sub Mtest() Dim LR As Long, i As Long LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count 'LR = Range(P Rows.Count).End(xlUp).Row Application.ScreenUpdating = False For i = LR To 1 Step -1 If Cells(i, 16).Value = 0 Then Cells(i, 16).EntireRow.Delete Next i Application.ScreenUpdating = True End Sub OR Sub Mtest1() Dim LR As Long, r As Long LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LR To 1 Step -1 If Application.WorksheetFunction.CountIf(Cells(r, 16), =0) = 1 Then Rows(r).Delete Next r Application.ScreenUpdating = True End Sub Thanks Mahesh On Thu, Aug 11, 2011 at 10:00 PM, Prabhu prabhugate...@gmail.com wrote: Hi Mahesh, It is deleting entire row wherever zero appearing. But i required in a partiuclar column if Zero value found we need to delete entire row. Ex. If we use base as P column, In 13th row of P column contains 0 value it has to delete entire 13th Row. It should not search other columns. Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row***
Sub Delete() On Error Resume Next With Range(A1:A5) .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = 0 Then .Rows(i).EntireRow.Delete = True End If Next i End With End Sub On Thu, Jul 28, 2011 at 9:54 PM, Prabhu prabhugate...@gmail.com wrote: Hi Friends, I Required Macro code if we find zero(0) in a particular row then we need to delete entire row. Example: InP' column P27,48,57 cells contain 0 then entire 27,48,57th row needs to delete from the sheet. Plz help Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row***
XLS S, I would tend to believe that progressing from small to large indices for a row will not solve the problem. A more conservative approach would be the classic reverse look that looks like this Sub DelRowWithZeroes() Dim lngLoop As Long With Worksheets(Sheet1).Range(P3:P13) For lngLoop = .Rows.Count To 1 Step -1 If .Cells(lngLoop).Value = 0 Then .Cells(lngLoop).EntireRow.Delete End If Next lngLoop End With End Sub Also, for posterity, please note that Excel 2007 and upper versions do not allow the use of Keywords like Add, Sort, Delete as names for Sub- procedures. On Jul 28, 9:30 pm, XLS S xlst...@gmail.com wrote: Sub Delete() On Error Resume Next With Range(A1:A5) .EntireRow.Hidden = False For i = 1 To .Rows.Count If WorksheetFunction.Sum(.Rows(i)) = 0 Then .Rows(i).EntireRow.Delete = True End If Next i End With End Sub On Thu, Jul 28, 2011 at 9:54 PM, Prabhu prabhugate...@gmail.com wrote: Hi Friends, I Required Macro code if we find zero(0) in a particular row then we need to delete entire row. Example: InP' column P27,48,57 cells contain 0 then entire 27,48,57th row needs to delete from the sheet. Plz help Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials athttp://www.excel-macros.blogspot.com 4. Learn VBA Macros athttp://www.quickvba.blogspot.com 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- . -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ ***Macro Code Required to delete Zero value cell and its row***
Hi Try Sub Mtest() Dim LR As Long, r As Long LR = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count Application.ScreenUpdating = False For r = LR To 1 Step -1 If Application.WorksheetFunction.CountIf(Rows(r), =0) = 1 Then Rows(r).Delete Next r Application.ScreenUpdating = True End Sub Thanks Mahesh On Thu, Jul 28, 2011 at 9:54 PM, Prabhu prabhugate...@gmail.com wrote: Hi Friends, I Required Macro code if we find zero(0) in a particular row then we need to delete entire row. Example: InP' column P27,48,57 cells contain 0 then entire 27,48,57th row needs to delete from the sheet. Plz help Regards, Prabhu -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
