Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
locate positive numbers and delete rows containing
I'm trying to isolate negative balances in a very large wookbook. Is there a
way to easily locate the positive balances and delete the rows containing them (leaving only the negative balances)? I've tried to mark with conditional formatting for 0 but the column titles get marked, too. Any assistance would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
locate positive numbers and delete rows containing
Your best bet would be filtering. Use DataFilterAdvanced Filter records
greater than zero. Now you have all the positive rows displayed, and you can delete them all. Turn off the filter and you're left with only the negative records. Note that it will take a while to delete the filtered records if you have a lot of them. Regards, Fred. "Giggly4g" wrote in message ... I'm trying to isolate negative balances in a very large wookbook. Is there a way to easily locate the positive balances and delete the rows containing them (leaving only the negative balances)? I've tried to mark with conditional formatting for 0 but the column titles get marked, too. Any assistance would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
locate positive numbers and delete rows containing
Sub deleterows()
Const columnBalance = "A" Dim rngBalance As Range Dim i As Long Set rngBalance = Range(Cells(1, columnBalance), _ Cells(Rows.Count, columnBalance).End(xlUp)) 'Work backwards from bottom to top when deleting rows Application.ScreenUpdating = False With rngBalance For i = .Rows.Count To 1 Step -1 If .Cells(i) < 0 Then .Cells(i).EntireRow.Delete End If Next i End With Application.ScreenUpdating = True End Sub "Giggly4g" wrote: I'm trying to isolate negative balances in a very large wookbook. Is there a way to easily locate the positive balances and delete the rows containing them (leaving only the negative balances)? I've tried to mark with conditional formatting for 0 but the column titles get marked, too. Any assistance would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
locate positive numbers and delete rows containing
Absolutely fabulous. A few little tweaks and it works perfectly. Thank you so
much! "Mike" wrote: Sub deleterows() Const columnBalance = "A" Dim rngBalance As Range Dim i As Long Set rngBalance = Range(Cells(1, columnBalance), _ Cells(Rows.Count, columnBalance).End(xlUp)) 'Work backwards from bottom to top when deleting rows Application.ScreenUpdating = False With rngBalance For i = .Rows.Count To 1 Step -1 If .Cells(i) < 0 Then .Cells(i).EntireRow.Delete End If Next i End With Application.ScreenUpdating = True End Sub "Giggly4g" wrote: I'm trying to isolate negative balances in a very large wookbook. Is there a way to easily locate the positive balances and delete the rows containing them (leaving only the negative balances)? I've tried to mark with conditional formatting for 0 but the column titles get marked, too. Any assistance would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete all negative numbers leaving only positive numbers | Excel Discussion (Misc queries) | |||
Excel, change column of negative numbers to positive numbers? | New Users to Excel | |||
How can I delete links (I can't locate) from one file to another? | Excel Discussion (Misc queries) | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions | |||
Locate and delete specific cells | Excel Discussion (Misc queries) |