MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Blank Rows to be found - Urgent


Posted by anand on April 13, 2001 7:44 AM

Hi All,

Through VBA , is there any way that i can loop throo an excel sheet row by row and write it out to a text file as a csv , can i ignore blank rows . the row needs to be blank only till say the 15th column , if there is records beyond that i still need to consider it as a blank line .

Any help will be greatly appreciated

Best Regards

Anand


Posted by Dax on April 14, 2001 6:05 AM

There is indeed a way to do this. Try some code like this:-

Sub WriteCSV()
Dim RangeUsed As Range
Dim sht As Worksheet
Dim r As Long, c As Integer
Dim BlankRow As Boolean

Set sht = ActiveSheet
Set RangeUsed = sht.UsedRange

Open "C:\Myfile.CSV" For Output As #1

For r = 1 To RangeUsed.Columns.Count
BlankRow = True

For c = 1 To RangeUsed.Rows.Count
If RangeUsed.Cells(r, c) <> "" Then
BlankRow = False
Exit For
End If
Next c
If BlankRow = False Then
For c = 1 To RangeUsed.Rows.Count
Print #1, RangeUsed.Cells(r, c).Value; ",";
Next c
Print #1,
End If
Next r

Close #1

End Sub