How to get my macro to stop at the last recognized line

[Coco1]

I have a macro that is comparing previous week's data with current weeks data to find differences. There are 7 sheets in the workbook:
New Raw Data (current weeks data imported),
NPI < 10 digits (looking to see if the NPI is less than 10 digits from current week's data), column B should have formula =len(a2) --> not recognizing last row in column A so has 0 after last row
Duplicate NPI (looking to see if there are duplicate NPI numbers in current week's data - conditional formatting to show any duplicates in red is on but the filter button is not on column A,
Blank NPI (looking for providers with no NPI) - is recognizing last row of data
Old Raw Data (previous week's data)
old not in new (comparing to see what differences are from previous vs current) - is recognizing last row of data
new not in old (comparing to see what differences are from current vs previous) - not recognizing last row of data

Sub Macro15()
Dim lr As Long
Sheets(Array("NPI < 10 digits", "Duplicate NPI", "Blank NPI", "Old Raw Data", _
        "old not in new", "new not in old")).Select
    Sheets("Blank NPI").Activate
    Cells.Select
    Selection.Delete Shift:=xlUp
    Sheets("New Raw Data").Select
    Cells.Select
    Range("U1").Activate
    Selection.Copy
    Sheets("Old Raw Data").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("New Raw Data").Select
    Cells.Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Dim ws As Worksheet, strFile As String
   
    Set ws = ActiveWorkbook.Sheets("New Raw Data") 'set to current worksheet name
   
    strFile = Application.GetOpenFilename
   
    With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileOtherDelimiter = "|"
        .Refresh
    End With
    Selection.AutoFilter
    Range("A1").Select
    Columns("AB:AB").Select
    ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort.SortFields.Add _
        Key:=Range("AB:AB"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("New Raw Data").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("NPI < 10 digits").Range("A1")
Sheets("NPI < 10 digits").Select
    Range("A:A").Select
    Selection.AutoFilter
    Range("A1").Select
   
    lr = Range("B" & Rows.Count).End(3).Row
   Range("B1").Select
    ActiveCell.FormulaR1C1 = "NPI Count"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=LEN(RC[-1])"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B" & lr)
    Range("B2:B" & lr).Select
    Range("B1").Select
    Selection.AutoFilter
    ActiveSheet.Range("A:B").AutoFilter Field:=2, Criteria1:="<10", _
        Operator:=xlAnd
Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")
Sheets("Duplicate NPI").Select
   Columns("A:A").Select
    Selection.FormatConditions.AddUniqueValues
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    Selection.FormatConditions(1).DupeUnique = xlDuplicate
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("Blank NPI").Range("A1")
Sheets("Blank NPI").Select
    Range("A1").Select
    Selection.AutoFilter
Sheets("New Raw Data").Range("A:AK").Copy Destination:=Sheets("new not in old").Range("A1")
Sheets("new not in old").Select
    Range("A1").Select
    Selection.AutoFilter
Sheets("Old Raw Data").Range("A:AK").Copy Destination:=Sheets("old not in new").Range("A1")
Sheets("old not in new").Select
    Range("A1").Select
    Selection.AutoFilter
    Sheets("old not in new").Select
    Range("A1").Select
    Sheets("new not in old").Select
    Columns("AB:AB").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("new not in old").Sort.SortFields.Add Key:=Range( _
        "A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("new not in old").Sort
        .SetRange Range("A2:AK" & lr)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    lr = Range("A" & Rows.Count).End(3).Row
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort.SortFields.Add Key _
        :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("new not in old").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    lr = Range("A" & Rows.Count).End(3).Row
    Sheets("old not in new").Select
    Selection.AutoFilter
    Columns("AB:AB").Select
    Selection.Cut
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort.SortFields.Add Key _
        :=Range("A1:A" & lr), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("old not in new").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    lr = Range("B" & Rows.Count).End(3).Row
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
    Sheets("new not in old").Select
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("B1").Select
    Sheets("old not in new").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'new not in old'!C[-1],1,FALSE)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B" & lr)
    Range("B2:B" & lr).Select
    Sheets("new not in old").Select
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],'old not in new'!C[-1],1,FALSE)"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B" & lr)
    Range("B2:B" & lr).Select
    Range("B1").Select

End Sub

 

[RobP]

Hi, did you step through the macro line by line (using F8 function key) to see which line its failing on ?

(when I say failing, I mean the line where you see the macro has done something you don't necessarily like ..)

I can't say its easy to follow, but I am guessing its somewhere around your "selecting" and "copying" of columns.. for example:

Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")

is going to select the entirety of column AB, to the last row in the workbook I believe, so it doesn't care about your last row of data. I'm not sure if this is where you see the problem or not ?

To find the last row of data in a document (meaning the last row of a specific column that has NO data below it) you could use something like :

last_row = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row

So this will store the last row of data from Col 5 ("E") inside "last_row". With that, you can then specify more accurately the size of the range you wish to copy and paste ..

It finds the last row by starting at the bottom of the worksheet in that specific column, and looks at each cell to see if its empty or not. In theory, all below are empty, so the first row it finds by default is the last row.

Hope it helps

Rob

 

[Coco1]

Hi, did you step through the macro line by line (using F8 function key) to see which line its failing on ?

(when I say failing, I mean the line where you see the macro has done something you don't necessarily like ..)

I can't say its easy to follow, but I am guessing its somewhere around your "selecting" and "copying" of columns.. for example:

Sheets("New Raw Data").Range("AB:AB").Copy Destination:=Sheets("Duplicate NPI").Range("A1")

is going to select the entirety of column AB, to the last row in the workbook I believe, so it doesn't care about your last row of data. I'm not sure if this is where you see the problem or not ?

To find the last row of data in a document (meaning the last row of a specific column that has NO data below it) you could use something like :

last_row = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row

So this will store the last row of data from Col 5 ("E") inside "last_row". With that, you can then specify more accurately the size of the range you wish to copy and paste ..

It finds the last row by starting at the bottom of the worksheet in that specific column, and looks at each cell to see if its empty or not. In theory, all below are empty, so the first row it finds by default is the last row.

Hope it helps

Rob

Hi RobP,
So I have the code

lr = Range("B" & Rows.Count).End(3).Row

which I thought would catch the last row but what is happening is when the formula is running for the new to old or old to new tabs if one section previously only had 297 rows then it only runs the formula to 297 instead of 303. Where should the code be placed in order for it to know to recognize this issue?

 

[RobP]

~So where you have this : (eg. the first time you use your lr code in your macro)

Sheets("NPI < 10 digits").Select
    Range("A:A").Select
    Selection.AutoFilter
    Range("A1").Select
   
    lr = Range("B" & Rows.Count).End(3).Row
   Range("B1").Select

It is looking for the last row of Column B on sheet "NPI < 10 digits".
Is that the correct sheet & column that you are looking for your last row of data ?

Maybe you can grab a screen shot of columns A to E, from rows 290-303 for us to see how your data looks. (change key data in case of sensitivity..)
But the point here is ColB MUST have data inside it (it cannot be blank, from rows 298 -> 303.)

The second time you try to execute your lr code further down your macro (here)

 End With
    lr = Range("A" & Rows.Count).End(3).Row
    Selection.AutoFilter
    Selection.AutoFilter

you are asking it to find lr from Column A, but this time on Sheet

"Sheets("new not in old").Select

from 17 lines of code above in your original post above here.

Your original lr value will be overwritten by whatever is on that sheet from Col A. Is that what you expect it to look at ? (and what is the last row of data in that column/sheet at the time it runs ?

Thats where I said above for you to step through your code using F8 line by line, as you can see what it does to your data as you go.

Then, the third time you run this line of code to get your last row once more, you are asking it to look in Col B for the last row (still from sheet "new not in old". )

Sorry I can't tell whats in your data during each step of this code .. only you can check that.

Hope its helpful in your debug

cheers
Rob

 

[Coco1]

So when I run the last part of the code for NPI <10 digits last week there were 438 rows and this week there were 436 rows so you will see in the image where the formula I am using =len(a2) is dropping down to the previous data and not current row data. I am unsure if my operations should be changed around in regards to how paste the data to each tab either.

Steps in the process:
1. NPI < 10 digits, Duplicate NPI, Blank NPI and Old Raw Data tabs are cleared out.
2. The data in New Raw Data is copied to Old Raw Data and then New Raw Data tab is cleared out.
3. New Raw Data tab is then looked at as the current worksheet and I import the new raw data from a query table to this tab.
4. Column AB from the New Raw Data tab is copied to NPI < 10 digits tab in column A.
5. Then a formula is added in column B on NPI < 10 digits but the formula does not pull down in my macro which is labeled NPI Count and formula = len(a2)
6. New Raw Data is copied to Blank NPI to check to see if column AB has any blanks
7. New Raw Data is copied to new not in old tab but column AB is copied first in column A then the entire dataset is copied
8. Old Raw Data is copied to old not in new tab but column AB is copied first in column A then the entire dataset is copied
9. In old not in new tab column B is added to do a vlookup to new not in old tab on Column A (this is filtered on column B to look at #N/As but the formula does not always recognize the last row)
10. In new not in old tab column B is added to do a vlookup to old not in new tab on column A (this is filtered on column B to look at #N/As but the formula does not always recognize the last row)

 

[RobP]

Hi, well, as I said, you are setting your "lastrow" three times in your macro, looking at different pages, and different columns.

I'm afraid I can't tell if where you are putting it is correct for you. So you need to identify the correct sheet / column that contains the correct number of rows, and use it there.

I'll repeat again, Step through your code (using [F8 Key] a line at a time, and where you get to a point that you are setting LastRow - hover the cursor over the "LastRow" variable in yoru code, and it will tell you the value thats stored in there, and then do again once that line is executed.

Then ask yourself if the value you see in there is what you want it to be. If not, then start to work out why ...

Sorry, but I'm blind to all your data.

Rgds
Rob

 

[Coco1]

Hi RobP,
Everytime I try to do step through it will not allow me to do so at all. I do not have the single step functionality on this excel. I have the Microsoft 365 version and using the desktop version.

Thanks,

Coty