I'm working on a VBA process which takes an excel file with a single tab full of training/qualifications data and cleans it up (removes duplicates, sorts, splits data into different tabs etc.).
I'm having issues with moving the last category of data from the original tab into it's dedicated tab.
Not working with VBA often my creation process was a combination of recording macro's and grabbing bits of example code from the internet, but since I hit my issue I have tried to break down the code examples I used, to understand them better, but still can't work out the cause of the problem.
I have a Sub for each category of training and filter the original list based on certification codes which relate to that category. As there may not be any rows for those certification codes in that set of data I check for the last populated row in the filtered list. If it is not equal to 1 (my heading row) I then I copy the visible rows and delete the originals.
This was working well until I reached the Sub for the last category of data. I found that although my filter result had multiple rows, my last row calculation always ended up as 1 and therefore the process never attempted the copy and delete section of the Sub. I thought this could be related to the category in question only having a single certification code and my last row filter method using an array, so I re-ordered my Subs putting a different category (with multiple certification codes to filter on) last. This was a Sub which was working as expected in it's original place in the process, but once I moved it to the end of the 'chain' of Subs it was no longer copying rows.
So it seems that once all other training category rows have been processed (effectively their data being removed from the master list) this breaks the last row method I'm using. But I don't understand why
Below is the Sub that was working until I moved it to the end of the process (and commented out the next Sub it was due to call) as an example
Any help gratefully received
- Sub CopyNetworkingRows()
- 'Select the master sheet
- Sheets("A-Row-level_Details_data (1)").Select
- 'Filter the rows by certification codes related to networking qualifications
- ActiveSheet.Range("$A$1:$BV$" & LastRow).AutoFilter Field:=7, Criteria1:=Array( _
- "CT_CCA-N", "CT_CCP-N", "CT_CCE-N", "CT_SDWAN"), Operator:=xlFilterValues
- 'Work out the new last row for virtualisation related rows
- NetLastRow = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
- 'Jump to end of sub if there are none of this/these certification codes in the list
- If NetLastRow <> 1 Then
- 'Select the visable (virtualisation certsificate) rows, copy and paste to virtualisation tab, then delete original rows
- Range("A2:O" & NetLastRow).SpecialCells(xlCellTypeVisible).Copy
- Sheets("Networking").Range("A2").PasteSpecial Paste:=xlValues
- Application.DisplayAlerts = False
- Range("A2:O" & NetLastRow).SpecialCells(xlCellTypeVisible).Delete
- 'resize columns to fit
- End If
- End Sub