.Rows.Count not working correctly?

[mikeoly]

Hi Everyone,
I have this code here that seems to be working great. I'm using it to output some data from a model, but I noticed that this code didn't catch all the populated rows. Any idea why the rows wouldn't be counted correctly? The .CSV file gets populated with data from A5:AW17, but what I really want is A5:AW19.

Also, please feel free to offer suggestions to improve my code in other ways!

Thanks!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim Msg As String, Ans As Variant
    Dim wline As Variant
    Dim r As Integer
    Dim lcol As Integer
    Dim strDir As String
    
    If Sheets("Sheet1").OLEObjects("CheckBox1").Object.Value = True Then
        
        
        wline = ""
        With Worksheets("Output")
        For r = 5 To .Cells(.Rows.Count, "A").End(xlUp).Row
            lcol = .Cells(r, 256).End(xlToLeft).Column
            On Error Resume Next
            wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine
        Next r
        End With
        
         
        'If current quarter folder doesn't exist, create it
        DataAsOf = Sheets("Output").Range("A2").Value
        
        strDir = Trim('\\yadadada" & DataAsOf)
        If Dir(strDir, vbDirectory) = NullString Then
            MkDir (strDir)
        Else: End If
        
        
        Open "file://yadadada" & DataAsOf & "\" & Sheets("Output").Range("C5").Value & " " & DataAsOf & " (" & Sheets("Output").Range("E5").Value & ")" & ".csv" For Output As #1 'Replaces existing file
        Print #1, wline
        Close #1
    Else
     Exit Sub
    End If
    
    
Quit:
End Sub

[Paul_Hossler]

Are A18 and A19 empty?

[mikeoly]

Are A18 and A19 empty?

I double checked the model to make sure they were populated. It seems to be an anomaly since it works fine for most of the other models.

[mperrah]

If you know you want to column AW19 every time try this:

    With Worksheets("Output") 
            lcol = 49 'same as AW
            For r = 5 To 19

                On Error Resume Next 
                wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine 
            Next r 
        End With

[Paul_Hossler]

1. If you comment out the On Error Resume Next, do you now get and error message?

2. In the code

  strDir = Trim( '\\yadadada" & DataAsOf)

Should that be a double quote\\yada....?

[mikeoly]

If you know you want to column AW19 every time try this:

    With Worksheets("Output") 
            lcol = 49 'same as AW
            For r = 5 To 19

                On Error Resume Next 
                wline = wline & Join(Application.Transpose(Application.Transpose(.Range("A" & r).Resize(, lcol))), ",") & vbNewLine 
            Next r 
        End With

This is certainly a solution as the range shouldn't be dynamic. Thanks for your input!

1. If you comment out the On Error Resume Next, do you now get and error message?

2. In the code

  strDir = Trim( '\\yadadada" & DataAsOf)

Should that be a double quote\\yada....?

1. I do get an error message for the line of code right after the 'On Error Resume Next'...Type Mismatch
2. Yup! I was just messing with the directory when typing up my post, fudged that a bit.

[mikeoly]

I'm marking this thread as solved. The mishap seemed to be an anomaly and I'll with mperrah's suggestion -- use a fixed range.