Excel Macro 1

[schnabs]

Hello,
I'm looking for some help with a Macro. It needs to be code because I need to run it 90 times per month, in the span of one day, so it needs to happen quick. I have columns A through G filled in a delinquency list for payments. If the cell in column G is 0.00 for a specific customer, I need to delete all of that customers charges. I need the code to run the entire length until the end of the list. I appreciate any feedback or help!

 

[SkipVought]

Hi,

What code do you have so far?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

I actually don't have any real code. I never have done much VB. The furthest I got was using some basic if functions to populate additional columns. When all is said and done, I need a code that will delete all rows between a set of data for the whole sheet.

My friend also sent this over, but said that it is missing a string command.

Sub FindZeroBal()

Dim LastRow As Integer

LastRow = ActiveSheet.UsedRange.Rows.Count

For Row = 18 To LastRow - 1

Value = Cells(Row, 7).Value

Formula = Cells(Row, 7).Formula

If InStr(Formula, "=ROUND") = 1 And Value = 0 Then

'parse the formula to get the row range to delete



End If

Next Row

End Sub

 

[SkipVought]

Can you use the AutoFilter to select the rows you want to delete?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

I could, but the only reason I want to use code is because I need to do this as quickly as possible, and also make it user friendly for people who have trouble making new folders in Windows. I need to be able to open the Excel sheet, run it, print, then close and do 90 more, as well as everyone else in my office be able to do the same.

 

[SkipVought]

This would be "as quickly as possible", because it does not happen in a LOOP as your posted code does.

Turn on your macro recorder and record selecting the rows you want to delete with the AutoFilter.

Select the visible ROWS of DATA and right-click > delete

Turn off your recorder and post your recorded code for help customizing.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

I'm actually not sure I can use the autofilter, unless there is a way I can delete rows and all rows in between my selection using autofilter, as there are rows between the two that come out of the filter I need to delete.

 

[ItIsHardToProgram]

I'm actually not sure I can use the autofilter, unless there is a way I can delete rows and all rows in between my selection using autofilter, as there are rows between the two that come out of the filter I need to delete. "

What?

"I can delete rows"

Yes you can delete rows.

"and all rows in between my selection using autofilter"

What? Between a selection? what?

"as there are rows between the two that come out of the filter I need to delete."

What? You need to delete a filter?



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[schnabs]

ItIsHardToProgram,
I'm not sure if you're actually trying to help, or just being cruel?? I admit maybe my previous post was a bit convoluted, but I'm pretty sure Skip can pick up what I'm saying, as we've been going back and forth.

 

[ItIsHardToProgram]

I am definatly not trying to be cruel, I am trying to undersand, weither I can help or not I shall determine that, but I am prety certain that Skip will have trouble understanding what you are trying to say.


Skip mentioned to "Select the visible ROWS of DATA", wich is why you are filtering, but then you say that you need to select rows that are filtered? wich means that not every row with a 0 need to be selected....

It is very unclear to me and probably unclear to him, if you would care to elaborate a bit more, he or I would be able to help alot more.

Sorry if my post sounded sarcastic or Ironic...



"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

Ok, I have a glimps of what you are trying to do, upon reading again.

You have debitors and creditors, and you want to eliminate all that balance out, so that you can find out what is missing:

the customers that don't balance for your month.


The best way I found to "merge" lines of data, wich was suggested by skip, was using a pivot table.

Unfortunatly pivot tables have limitations, so skip suggested using MS query, wich makes more sense since you are flexible onto what SQL command you want to write.

___________________________

Maybe we can find even a better way to do this, what kind of accounting program do you use?


HTH,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[schnabs]

No problem. I apologize if I came off being defensive at all. Coding is very frustrating to me. I was responding to Skips earlier inquiry as to whether or not I can use an autofilter. Well I don't believe I can. When I use the autofilter, I can get the beginning and ending row of the range of data I need to delete. But what I was trying to say , was that I need to delete the whole range, not just the visible rows. I wasn't sure if there was someway when using autofilter, I can delete a range of data, not just visible rows.
Thanks

 

[SkipVought]

I stated, "Turn on your macro recorder and record selecting the rows you want to delete with the AutoFilter."

Maybe that was not clear. Use the AutoFilter CRITERIA to DISPLAY the rows that you wish to DELETE.

Is that something that you can do?



Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Ok, not what I thought, but now I unnderstand.

Yes there is a way to do this.

BTW this is untested and might have a few needs of fixes, but I am on a rush.

I am not sure you can delete a range, you might need to .select then Selection.delete (but this is unefficient as I come to understand now)

Also, Skip's method is probably better, but to what you explained to me, this is what I would do if I HAD to use a loop.

This is not efficient.


Using what you have already provided:

Sub FindZeroBal()

    [!]Dim iLastRow As Integer, iRow As Integer, iRowNum As Integer
    Dim dValue As Double
    Dim iRangeRows As Integer[/!]


    iLastRow = ActiveSheet.UsedRange.Rows.Count


    For iRow = 18 To iLastRow - 1
       dValue = Cells(row, 7).Value
       
       If InStr(Cells(row, 7).Formula, "=ROUND") = 1 And Value = 0 Then
            
           For iRowNum = iRow To LastRow - 1
               If Cells(iRowNum,7).Value = [Paste here what the value is when you want to stop your range]
                      iRangeRows = iRowNum
                      Exit For
               End If
           Next iRowNum
           
           [!]Worksheets([NameOfYourWorksheet]).Range(Cells(iRow, 1), Cells(iRowNum, 7)).Delete[/!]
           
       End If

    Next row

    
End Sub

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

If the cell in column G is 0.00 for a specific customer, I need to delete all of that customers charges. I need the code to run the entire length until the end of the list.

This stated criteria can be displayed in AutoFilter AND the displayed range of data can be deleted in on fell swoop.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ItIsHardToProgram]

Skip, I think that the charges won't have "0" in the filtered cell, wich is why they would not appear, and wich is why he would need to process through a loop, or populate 0 to every cell in the lines of the specific client, and then use the autofilter method.

Of course this is as I come to understand it with the information he has provided...

But the code he suggested in fact did not seem to mean that...

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[ItIsHardToProgram]

The charges being lines, and not columns.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[SkipVought]

IHTP,

Perhaps you should wait to see how the OP responds, rather than making assumptions.

Your posts seem to have clouded the discussion.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[schnabs]

I really appreciate all of the help guys

ItsHardToProgram
The code you wrote threw an error on the line
If Cells(iRowNum,7).Value = [Paste here what the value is when you want to stop your range]

Skip:
Yes I will record the Macro soon. I might not have a computer available for an hour or two so it might be later. But I'm going to try to represent what my sheet looks like.

Customer Name 2
Charge 1
Charge 1
Charge 1
Customer Total 2
The 2's and 1's are not the amount, but the result of some functions I used. If I use autofilter, I can delete Customer Name and Customer Total, but from what I've seen, I cannot delete nor select the rows that contain 1, which I also need to. I make it clear, if the customer total column is 0.00, I need to delete every row between customer name and customer total.
Thanks again guys.

 

[SkipVought]

Your "table" is not a proper TABLE.

Each row must contain ALL the data (customer name) for Excel feature to work. You're shooting yourself in the foot!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}