J
JE
A colleague wrote vba code and has left town for 2 weeks before testing. I
believe I identified the correct section of code causing the problem however,
I am only learning vba programming. This may be out of scope for this forum
but is someone able to read the code and determine if it appears to be
correct. If yes, I will continue troubleshooting.
The file that is to be manipulated looks like:
A B C
D E
563 ACCOUNT: 4MV159560 SHORT NAME: SMITH
581 ACCOUNT: 4MV177935 SHORT NAME: WACKER
601 ACCOUNT: 4MV202709 SHORT NAME: LAITINEN
661 ACCOUNT: 4MV202709 SHORT NAME: LAITINEN
676 ACCOUNT: 4MV215032 SHORT NAME: HIEB
What the code should do is:
IF Col C for given line matches Col C for the row above, delete the line.
For instance, the row beginnign with 661 (in col A) would be deleted because
Col C in the line above (601 in col A) matches.
The code written is:
Dim wks As Worksheet
Dim rngFound As Range
Dim RngToSearch As Range
Dim EndCell As Range
Set wks = Worksheets(4)
Set RngToSearch = wks.Columns("b")
Set rngFound = RngToSearch.Find(What:="ACCOUNT:", LOOKAT:=xlPart)
Range("a1", rngFound.Offset(-1, 0)).EntireRow.Delete
Cells(Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row).Select
Set RngToSearch = wks.Columns("b")
Set rngFound = RngToSearch.Find(What:="ACCOUNT:", LOOKAT:=xlPart,
searchdirection:=xlPrevious)
a = Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row
Set EndCell = Cells(a, 2)
Range(rngFound.Offset(1, 0), EndCell).EntireRow.Delete
Range("a1").Select
Range("c1").Select
Selection.CurrentRegion.Sort Key1:=Range("C1"), Order1:=xlAscending,
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
acctnum = Range("c1").Value
For b = 2 To Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Cells(b, 3) = acctnum Then
Rows(b).Delete
b = b - 1
End If
acctnum = Cells(b, 3)
Next b
When the query was run, it looped and finally errored due to no records
retrieved.
Again, if this question is out of scope, please let me know. Thank you.
believe I identified the correct section of code causing the problem however,
I am only learning vba programming. This may be out of scope for this forum
but is someone able to read the code and determine if it appears to be
correct. If yes, I will continue troubleshooting.
The file that is to be manipulated looks like:
A B C
D E
563 ACCOUNT: 4MV159560 SHORT NAME: SMITH
581 ACCOUNT: 4MV177935 SHORT NAME: WACKER
601 ACCOUNT: 4MV202709 SHORT NAME: LAITINEN
661 ACCOUNT: 4MV202709 SHORT NAME: LAITINEN
676 ACCOUNT: 4MV215032 SHORT NAME: HIEB
What the code should do is:
IF Col C for given line matches Col C for the row above, delete the line.
For instance, the row beginnign with 661 (in col A) would be deleted because
Col C in the line above (601 in col A) matches.
The code written is:
Dim wks As Worksheet
Dim rngFound As Range
Dim RngToSearch As Range
Dim EndCell As Range
Set wks = Worksheets(4)
Set RngToSearch = wks.Columns("b")
Set rngFound = RngToSearch.Find(What:="ACCOUNT:", LOOKAT:=xlPart)
Range("a1", rngFound.Offset(-1, 0)).EntireRow.Delete
Cells(Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row).Select
Set RngToSearch = wks.Columns("b")
Set rngFound = RngToSearch.Find(What:="ACCOUNT:", LOOKAT:=xlPart,
searchdirection:=xlPrevious)
a = Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row
Set EndCell = Cells(a, 2)
Range(rngFound.Offset(1, 0), EndCell).EntireRow.Delete
Range("a1").Select
Range("c1").Select
Selection.CurrentRegion.Sort Key1:=Range("C1"), Order1:=xlAscending,
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom,
DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
acctnum = Range("c1").Value
For b = 2 To Worksheets(4).UsedRange.SpecialCells(xlCellTypeLastCell).Row
If Cells(b, 3) = acctnum Then
Rows(b).Delete
b = b - 1
End If
acctnum = Cells(b, 3)
Next b
When the query was run, it looped and finally errored due to no records
retrieved.
Again, if this question is out of scope, please let me know. Thank you.