Excel to CSV export every two rows

Hi I have some code for exporting worksheet ranges to CSV files this works well, now I need to change my code to export every two lines to a separate CSV file until a certain range, I do not want to create my codes 150 times, so think this should be possible with some type of loop statement.

Sub Booktest()

Dim mytext As String
mytext = Range("I36").Text

 Dim ColNum As Integer
  Dim Line As String
  Dim LineValues() As Variant
  Dim OutputFileNum As Integer
  Dim PathName As String
  Dim RowNum As Integer
  Dim SheetValues() As Variant
  
  

  

  PathName = "c:\test"
  OutputFileNum = FreeFile


  Open PathName & "\TESTBOOK" & "-" & mytext & ".csv" For Output Lock Write As #OutputFileNum
 

  SheetValues = Sheets("FAB.csv").Range("BG4:CD192").Value
  ReDim LineValues(1 To 24)

  For RowNum = 1 To 188
    For ColNum = 1 To 24
      LineValues(ColNum) = SheetValues(RowNum, ColNum)
    Next
    Line = Join(LineValues, ",")
    Print #OutputFileNum, Line
  Next

  Close OutputFileNum
  
  End Sub

Open in new window

rhill52Asked:
Who is Participating?
 
Ejgil HedegaardCommented:
To test for values in the range, insert in line 22.
If WorksheetFunction.CountA(Range(("BH" & f) & ":" & ("CE" & t))) > 0 Then

Move "Close OutputFileNum" to line 40, and add "End If" in line 41
0
 
Ejgil HedegaardCommented:
If you by every two lines mean line 1, 3, 5 etc. then change line 28 in the code
For RowNum = 1 To 188
to
For RowNum = 1 To 188 Step 2

But if you mean something else, please specify.
0
 
rhill52Author Commented:
Hi currently the code will take the range 1 to 188 and export to a CSV file, what I need to do is take the range 1 - 2 export to CSV then 3 - 4 export to CSV and so on until we get to row 188 giving me many CSV files to import later.

Thanks
0
 
rhill52Author Commented:
ok I managed to work it out  which creates my files for me, but would like to only generate the CSV files if they contain data in the two rows is this possible?.

Sub testCount()

Dim mytext As String
mytext = Range("I36").Text

 Dim ColNum As Integer
  Dim Line As String
  Dim LineValues() As Variant
  Dim OutputFileNum As Integer
  Dim PathName As String
  Dim RowNum As Integer
  Dim SheetValues() As Variant
  
  Dim f As Integer
  Dim t As Integer


  f = 4
  t = 5

Do Until t = 379

  PathName = "c:\test"
  OutputFileNum = FreeFile


  Open PathName & "\test" & f & "-" & mytext & ".csv" For Output Lock Write As #OutputFileNum
 

  SheetValues = Sheets("test.csv").Range(("BH" & f) & ":" & ("CE" & t)).Value
  ReDim LineValues(1 To 24)

  For RowNum = 1 To 2
    For ColNum = 1 To 24
      LineValues(ColNum) = SheetValues(RowNum, ColNum)
    Next
    Line = Join(LineValues, ",")
    Print #OutputFileNum, Line
  Next


  f = f + 2
  t = t + 2
  
  Close OutputFileNum
  
 
  Loop
  
  
  
  End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.