[VBA] Output Text File - Multiple Range - Ignore Blanks

[addms]

Hello!

I have finally registered an account on this forum after ending up here after various searches throughout my experience with Excel. It seems like a great, helpful community and I have almost always been able to find an answer on here!

I'm a little stuck with this one, so either my searching isn't quite up to scratch or it hasn't been covered. I presume it's something really simple but I can't wrap my head around it!

I have a spreadsheet with all of my frequently purchsaed products on it from a certain supplier. We order twice a week, Tuesday and Thursday. They have an automated ordering system where you can just enter the SKU and quantity of each item on a line, either on their online UI or by uploading a text file. For example's sake, let's say they have 100 products that we frequently order but depending on requirements for the week we may only order 50 from this list.

The uploaded image shows the SKU listed in Columns X for the Tuesday delivery and Column Y for the Thursday delivery. I am aware I could have these all in one column and select a day but I have intentionally kept them seperate as they are treated as two seperate suppliers on a different unrelated platform.

The Quantity selection is Column T where we will enter what we would like to order. By entering a quantity here this will respectively populate Columns AU & AV with the format required to order using their 'quick ordering' software, which is "SKU [space] Quantity" - If either SKU or Quantity is blank it will not populate this column. The formula for these is:

=IF(OR(ISBLANK($T3),ISBLANK(X3),$T3="",X3=""),"",X3&" "&$T3)

I then have a button set up to run a script I entered to output these to a text file, which is as follows:

Sub BestpetsGenerateTuesday()
    Dim filename As String, lineText As String
    Dim myrng As Range, i, j
    
    filename = "C:\Users\Adam\Desktop\BestpetsUpload1.txt"
    
    Open filename For Output As #3
    
    Set myrng = Range("AU2:AU9999")
    
    For i = 1 To myrng.Rows.Count
        For j = 1 To myrng.Columns.Count
                lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)
          Next j
            If Trim(lineText) <> "" Then
            
        
        Print #3, lineText
        End If
    Next i
    
    Close #3
End Sub

Sub BestpetsGenerateThursday()
    Dim filename2 As String, lineText2 As String
    Dim myrng2 As Range, i, j
    
    filename2 = "C:\Users\Adam\Desktop\BestpetsUpload2.txt"
    
    Open filename2 For Output As #4
    
    Set myrng2 = Range("AV2:AV9999")
    
    For i = 1 To myrng2.Rows.Count
        For j = 1 To myrng2.Columns.Count
                lineText2 = IIf(j = 1, "", lineText2 & ",") & myrng2.Cells(i, j)
          Next j
            If Trim(lineText2) <> "" Then
            
        
        Print #4, lineText2
        End If
    Next i
    
    Close #4
End Sub

The criteria I wanted for the code was to
- Copy every value in AU or AV whilst skipping the blank lines. This then creates a text document formatted as follows:
179504 1
573010 2
573008 3
164013 3
535522 15
111553 5

Now, the thing is, everything works. It does as it should and functions well. The question I'm asking is how I can adjust this so that I can reduce the amount of formulas in the worksheet itself and rely more on VBA? Currently there are around 600 rows in the worksheet, which is fine, but I intend on adding other suppliers into this sheet and copying the function for those respectively and would care to have more plain text rather than this formula in many cells. I'm planning to roll out this spreadsheet onto a slower system I have with pretty basic specs, so something fairly light to run would be ideal.

If somebody could please let me know how I can expand my VBA formula above to set the range as SKU [space] Quantity whilst EXCLUDING lines that are unpopulated.

Thank you!