PDA

View Full Version : VBA cut-and-paste code stopped working 100%?



dgr7
05-01-2007, 10:22 AM
hello,
I have the below VBA code snippet that I use to automatically cut out rows from a large spreadsheet based on the criteria of if FINALBILL-BALANCE is
negative. The making of that worksheet works fine. Where I'm running into problems is this portion of the snippet

Set DelSH = Sheets("cabnewb")
With Sheets("Balance Exceeds Total Charges")
For Each ce In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
If WorksheetFunction.CountIf(DelSH.Range("A:A"), ce) > 0 Then
Set findit = DelSH.Range("a:a").Find(What:=ce, LookAt:=xlWhole)
DelSH.Cells(findit.Row, 1).EntireRow.Delete
End If
Next ce
End With

that deletes the rows from the cabnewb worksheet that were placed on the Balance Exceeds Total Charges worksheet. For some reason it isn't working 100% and some rows are left behind on the cabnewb worksheet that shouldn't be and instead some rows that shouldn't be deleted are. What just recently happened to me was
(1) 2 out of 4 rows placed on the Balance Exceeds Total Charges were left behind when all 4 rows should've been deleted from cabnewb
(2) two rows that were completely unrelated to the any of the 4 on the Balance Exceeds Total Charges worksheet (as far as I understand the code to work....I think the "entire" row has to match between the two worksheets and the two that were erroneously deleted I didn't see any common data in them from spotchecking against the four) were delete from cabnewb, so there was a total of four rows delete, two were correctly delete and two weren't.

Also, I've run my code several times, and every time it's the same two rows that are correctly deleted from cabnewb, the same two rows that are erroneously left behind on cabnewb, and the same two that are erroneously deleted from cabnewb that shouldn't be.

What really puzzles me about all of this is that I've used this type of auto-cut-and-paste code for months in many larger VBA subroutines with problems where I just modify the worksheet names and cutting criteria and have never had any problems with it....until this week!?

Can anyone show me either how to fix this code or a better VBA solution that I could replace it with? I need something that'll work 100% of the time.

thanks in advance,
david



Dim LastRow As Long
LastRow = Range("a65536").End(xlUp).Row

Sheets("cabnewb").Select
LastRow = Range("a65536").End(xlUp).Row
Sheets.Add.Name = "Balance Exceeds Total Charges"
Sheets.Add.Name = "filter criteria"

Sheets("filter criteria").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "FINALBILL-BALANCE"
Range("A1:A1").Select
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("A2").Select
ActiveCell.FormulaR1C1 = "<0"

Sheets("Balance Exceeds Total Charges").Select
Sheets("cabnewb").Range("A1:AD" & LastRow).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("filter criteria").Range("A1:A2"), CopyToRange:=Range("A1"), _
Unique:=False

Worksheets("Balance Exceeds Total Charges").Activate
If Cells(2, 5) = "" Then
Worksheets("Balance Exceeds Total Charges").Delete
Else
Set DelSH = Sheets("cabnewb")
With Sheets("Balance Exceeds Total Charges")
For Each ce In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row)
If WorksheetFunction.CountIf(DelSH.Range("A:A"), ce) > 0 Then
Set findit = DelSH.Range("a:a").Find(What:=ce, LookAt:=xlWhole)
DelSH.Cells(findit.Row, 1).EntireRow.Delete
End If
Next ce
End With
Cells.Select
Cells.EntireColumn.AutoFit
End If

Sheets("filter criteria").Select
ActiveWindow.SelectedSheets.Delete

austenr
05-01-2007, 11:52 AM
Dont have time to re code this for you but when you are deleting rows you have to work from the bottom up or you will have the problem you are describing. Like this:

For R = Rng.Rows.Count To 2 Step -1

dgr7
05-07-2007, 07:45 AM
austenr,
thank you! yes, that does make sense to walk backwards through the rows...I've done that before in other loops, but this particular loop I acquired off of another message board and hadn't modified it much...it had seemed to be working until now. Anyway, I just received some code from a person on another msg board where they rewrote my code completely so it now uses Autofilter and it works great!
thanks again,
david