.Rows.Count not working correctly?

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!

Code:

---

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

---

Are A18 and A19 empty?

Quote:

---

Originally Posted by Paul_Hossler

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.

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

Code:

---

    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

---

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

2. In the code

Code:

---

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

---

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

Quote:

---

Originally Posted by mperrah

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

Code:

---

    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!

Quote:

---

Originally Posted by Paul_Hossler

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

2. In the code

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.

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.