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

A

anteween

New Member
Joined
Nov 18, 2016
Messages
3
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 586"]
<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>[TR]
[TD="class: xl69, width: 257, bgcolor: transparent"]Aliquot Number[/TD]
[TD="class: xl69, width: 60, bgcolor: transparent"]Analyte[/TD]
[TD="class: xl69, width: 98, bgcolor: transparent"]Measured Value[/TD]
[TD="class: xl69, width: 84, bgcolor: transparent"]Analysis Date[/TD]
[TD="class: xl69, width: 86, bgcolor: transparent"]Analysis Time[/TD]
[TD="class: xl69, width: 111, bgcolor: transparent"]Analyst[/TD]
[TD="class: xl69, width: 84, bgcolor: transparent"]Dilution factor[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12345[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]2.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12346[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]3.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12347[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]4.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12348[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]5.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12349[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]6.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12350[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]7.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12351[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]8.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12352[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]9.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12353[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]10.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12354[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]11.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12355[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]12.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12356[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]13.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12357[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]14.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12358[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]15.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent, align: right"]12359[/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]16.2[/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"]NH3 as N[/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent, align: right"]11/17/2016[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]13:00[/TD]
[TD="class: xl67, bgcolor: transparent"]Sarah[/TD]
[TD="class: xl67, bgcolor: transparent, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Try this:

Replace your code
Code: 
[COLOR=#333333]LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row[/COLOR]
with
Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

airforceone
  • Solved
Search Row 1 for Text if Found Convert Column to Date
2
Replies
11
Views
1K
airforceone
airforceone
youbitto
  • Solved
Copy paste from multiple Excel and Csv files to another excel file VBA
Replies
2
Views
181
youbitto
youbitto
J
  • Solved
How to get the Range of Cells with "Values" within a Range of formulas
Replies
4
Views
355
jrtraylor
J
S
  • Question Question
How to create a proper excel table from unorganized data?
Replies
5
Views
321
Burrgogi
Burrgogi
A
  • Question Question
Calculate the average of a column with time excluding rows with values in other columns
Replies
4
Views
660
arthurbr
arthurbr

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top