.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.... (file://\\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.... (file://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.