vba code to create a csv file - how to choose rows with data in column A

[anteween]

Aliquot Number Analyte Measured Value Analysis Date Analysis Time Analyst Dilution factor 12345 NH3 as N 2.2 11/17/2016 13:00 Sarah 1 12346 NH3 as N 3.2 11/17/2016 13:00 Sarah 1 12347 NH3 as N 4.2 11/17/2016 13:00 Sarah 1 12348 NH3 as N 5.2 11/17/2016 13:00 Sarah 1 12349 NH3 as N 6.2 11/17/2016 13:00 Sarah 1 12350 NH3 as N 7.2 11/17/2016 13:00 Sarah 1 12351 NH3 as N 8.2 11/17/2016 13:00 Sarah 1 12352 NH3 as N 9.2 11/17/2016 13:00 Sarah 1 12353 NH3 as N 10.2 11/17/2016 13:00 Sarah 1 12354 NH3 as N 11.2 11/17/2016 13:00 Sarah 1 12355 NH3 as N 12.2 11/17/2016 13:00 Sarah 1 12356 NH3 as N 13.2 11/17/2016 13:00 Sarah 1 12357 NH3 as N 14.2 11/17/2016 13:00 Sarah 1 12358 NH3 as N 15.2 11/17/2016 13:00 Sarah 1 12359 NH3 as N 16.2 11/17/2016 13:00 Sarah 1 NH3 as N 11/17/2016 13:00 Sarah 1 NH3 as N 11/17/2016 13:00 Sarah 1 NH3 as N 11/17/2016 13:00 Sarah 1 NH3 as N 11/17/2016 13:00 Sarah 1 NH3 as N 11/17/2016 13:00 Sarah 1 NH3 as N 11/17/2016 13:00 Sarah 1 NH3 as N 11/17/2016 13:00 Sarah 1 NH3 as N 11/17/2016 13:00 Sarah 1 <colgroup><col width="257" style="width: 193pt; mso-width-source: userset; mso-width-alt: 9398;"> <col width="60" style="width: 45pt; mso-width-source: userset; mso-width-alt: 2194;"> <col width="98" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3584;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <col width="86" style="width: 65pt; mso-width-source: userset; mso-width-alt: 3145;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"> <tbody> </tbody>

<tbody>
</tbody>

I have a worksheet above(Upload), which gets data from another worksheet (Batch) in the same workbook.
My vba code creates a csv file (code below) However, when the file is created, it creates a line for the rows that have nothing in column A.
How do I change my code to only select data if there is a 'character' in column A? For example, looking at the above table, I would only want the first 15 rows of data.


Sub WriteCSVTitanUpload()

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row
'LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row (I was trying different codes here)
CellData = ""

FilePath = "H:\Redirection\Documents\ATL Implementation\NH3Upload.csv"

Open FilePath For Output As #2

For i = 1 To LastRow

For J = 1 To LastCol

If J = LastCol Then
CellData = CellData + Trim(ActiveCell(i, J).Value)
Else
CellData = CellData + Trim(ActiveCell(i, J).Value) + ","
End If


Next J

Write #2, CellData
CellData = ""

Next i

Close #2

MsgBox ("Done")


End Sub

 

[portews]

Try this:

Replace your code

[COLOR=#333333]LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row[/COLOR]

with

LastRow = [COLOR=#333333]ActiveSheet[/COLOR].Cells([COLOR=#333333]ActiveSheet[/COLOR].Rows.Count, "A").End(xlUp).Row

The function you have finds the furthest column right and returns that column letter and the furthest column down and returns that row, even if there is nothing in that actual cell.

The code I added goes to the very bottom of the worksheet column A, then do an "End" "Up", which will find the last cell in A that actually has a value in it.

 

[anteween]

Thanks for the advice Bill. But now I get a bunch of comma's and no data.

 

[anteween]

Sorry, my bad. I was not in the correct cell.