Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Hello, I need to create a macro to delete an entire row if a cell
value in a selected cell of that row has a zero value. Any suggestions? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Try something like
Sub AAA() If IsEmpty(ActiveCell.Value) = False Then If IsNumeric(ActiveCell.Value) = True Then If ActiveCell.Value = 0 Then ActiveCell.EntireRow.Delete xlShiftUp End If End If End If End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "amanda" wrote in message ups.com... Hello, I need to create a macro to delete an entire row if a cell value in a selected cell of that row has a zero value. Any suggestions? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Try searching this NG. But if you're too lazy to do that start with this:
Sub DeleteRows() Const StartRow As Long = 1 'Row to Start looking at Const StopRow As Long = 65000 'Row to Stop looking at Const Col As Long = 2 'Column to search for 0 in Dim cnt As Long For cnt = StopRow to StartRow Step -1 If Cells(cnt,Col) = 0 Then Rows(cnt).Delete Next End Sub -- Charles Chickering "A good example is twice the value of good advice." "amanda" wrote: Hello, I need to create a macro to delete an entire row if a cell value in a selected cell of that row has a zero value. Any suggestions? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
Hi Charles,
I would give the macro a much more descriptive name of what rows are being deleted. Simply going through 65000 rows is not going to be efficient, nor is it correct. While your version may have 65536 rows that restriction will be upped considerably in Excel 2007, and I presume nobody is still using Excel 95. But going through 65536 cell comparisons on my computer would take a few minutes, if you have only 300 rows, it should go extremely fast even on my computer when it had 128MB RAM. col = activecell.column StopRow = Cells(Rows.Count, col).End(xlUp)).Row Changed it from Column B to the column of the activecell to make the macro more generic. Sub Del_rows_with_zero_in_column_of_activecell() Const StartRow As Long = 1 'Row to Start looking at Dim StopRow As Long Dim Col As Long Col = ActiveCell.Column StopRow = Cells(Rows.Count, Col).End(xlUp).Row Dim cnt As Long For cnt = StopRow To StartRow Step -1 If IsNumeric(Cells(cnt, Col)) Then If Cells(cnt, Col) = 0 Then Rows(cnt).Delete End If Next End Sub Additional references: (the first is on Slow Response) http://www.mvps.org/dmcritchie/excel/slowresponse.htm http://www.mvps.org/dmcritchie/excel/delempty.htm http://www.mvps.org/dmcritchie/excel/toolbars.htm --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "Charles Chickering" wrote in message ... Try searching this NG. But if you're too lazy to do that start with this: Sub DeleteRows() Const StartRow As Long = 1 'Row to Start looking at Const StopRow As Long = 65000 'Row to Stop looking at Const Col As Long = 2 'Column to search for 0 in Dim cnt As Long For cnt = StopRow to StartRow Step -1 If Cells(cnt,Col) = 0 Then Rows(cnt).Delete Next End Sub -- Charles Chickering "A good example is twice the value of good advice." "amanda" wrote: Hello, I need to create a macro to delete an entire row if a cell value in a selected cell of that row has a zero value. Any suggestions? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Rows
correction must check the cell that it is not empty as well
like Chip indicated in his reply. Sub Del_rows_with_zero_in_column_of_activecell() Const StartRow As Long = 1 'Row to Start looking at Dim StopRow As Long Dim Col As Long Col = ActiveCell.Column StopRow = Cells(Rows.Count, Col).End(xlUp).Row Dim cnt As Long For cnt = StopRow To StartRow Step -1 If Not IsEmpty(Cells(cnt, Col)) Then If IsNumeric(Cells(cnt, Col)) Then If Cells(cnt, Col) = 0 Then Rows(cnt).Delete End If End If Next cnt End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
Help!!! I have problem deleting 2500 rows of filtered rows | Excel Programming |