Macro to delete some rows meeting criteria

[KC Rippstein]

Hello. I would really appreciate some help with writing a macro, please.

I have a report that imports data from another system as a list. It
contains upwards of 20,000 rows and can be considerably consolidated, but it
has issues. I filter for zero dollar values on the money amount column, hit
F5-Special-Visible and try to delete the rows but it doesn't work (it either
tells me the task is too complex or it deletes all but one record).

So I pulled a simple macro off the forum here that looks for zero values in
the column I want on a row by row basis.

Sub Delete_ZeroValue_Rows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
Dim lastrow As Long, r As Long
lastrow = Cells(Rows.Count, "F").End(xlUp).Row
For r = lastrow To 1 Step -1
If Cells(r, "F").Value = 0 Then
Rows(r).EntireRow.Delete
End If
Next r
ActiveSheet.DisplayPageBreaks = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

This works well and gets my list cut in half within 30 seconds. Here's what
else I need, though:
1) I'd like to change it from being fixed to column F to instead ask me for
the money column.
2) I'd like it to show me an application status bar and then tell me how
many rows were deleted (Chip Pearson has something like this, but I'm not
sure how to plug it into this code correctly...it gave me error 400 when I
attempted it myself).
3) I then need to filter the same money column for positive and negative
values that cancel each other out within the same month. I suppose it
should ask me what column contains the dates, sort the data by month, then
look for positive/negative cancellation entries within that month's block of
data (the canceling entries may not necessarily be in order one right after
the other but may be separated by a couple of entries).

Thanks a bunch!!
KC Rippstein

 

[Bob Phillips]

Assuming you have Chip's DLL (I couldn't download it), this should address
the first two points

Dim Prog As ProgressReporter.Progressor

Sub Delete_ZeroValue_Rows()
Application.Calculation = xlCalculationManual
ActiveSheet.DisplayPageBreaks = False
Dim lastrow As Long, r As Long
Dim N As Long, col As Range

On Error Resume Next
Set col = Application.InputBox("Select target column with mouse",
Type:=8)
On Error GoTo 0

If Not col Is Nothing Then

If Prog Is Nothing Then Set Prog = New ProgressReporter.Progressor

With Prog

Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, col.Column).End(xlUp).Row

.MinimumValue = 0: .MaximumValue = 1000
.SetParentWindow 0: .CurrentValue = 0
.Show

For r = lastrow To 1 Step -1

If Cells(r, col.Column).Value = 0 Then
Rows(r).EntireRow.Delete
End If

DoEvents
.Increment 1
Next r

End With

Set Prog = Nothing

End If

ActiveSheet.DisplayPageBreaks = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Don Guillett]

Fries too?

 

[KC Rippstein]

I hope you jest, as I certainly didn't mean to impose on anyone. I did get
two books for Christmas: Walkenbach's Power Programming with VBA and another
book called VBA and Macros for Microsoft Excel, so I hope to learn VBA
myself in the coming months.

It just appeared the macro should be able to remove redundant entries that
cancel each other out, but since the data ultimately gets analyzed by pivot
table which feeds off of the entire columns (since I never know the last row
from month to month), then I don't necessarily have to remove the extra
4,000 rows or so of canceling entries. The pivot can easily work with the
9,000 rows I have. I just paste the new month's data over the old and
refresh my pivot and it works well.

This newsgroup is a big blessing to many. I hardly ever have to ask a
question in here because so many incredible answers are already out there.
I wish all of you MVPs and other contributors a happy new year!

 

[KC Rippstein]

Thanks, Bob. You have addressed my main concern about allowing me to select
the appropriate "money" column, but that part of the code doesn't work. The
line to set the col and invoke an input box highlights itself red as soon as
I paste your code in. Should I be putting this macro into a Module instead
of the default area that shows up when I right click the tab and view code?

 

[Dave Peterson]

Bob's code got hit by linewrap.

This should be all one line (or continued on two with an underscore and space
character):

Set col = Application.InputBox("Select target column with mouse", _
Type:=8)