Create Dynamic Range in Excel

[pmclinn]

The code below takes the data from the S column and grabs all the data
and graphs it. The problem is this example grabs 500 cells worth of
data and some of these cells = nothing. How do I rescope a range to
prevent the data that is = nothing not to display....

Dim app As New Excel.Applicati on
' app.Visible = True
Dim Doc As Excel.Workbook =
app.Workbooks.O pen("C:\Filenam e.csv")
Dim Sheet As Excel.Worksheet = Doc.Sheets(1)
app.DisplayAler ts = False

app.Range("S1:S 500").Select()
Dim r As Excel.Range = app.Selection
Dim s As String
Dim int As Integer
For j As Integer = 1 To 500
s = r(j, 1).value
' txtImport.Text &= s & vbTab
If s = String.Empty Or s = Nothing Or s = " " Then
int = j
Exit For
End If
Next

Dim Chart As Excel.Chart = Doc.Charts.Add( , Sheet)
Chart.Name = "TX Graph"

Chart.ChartType = Excel.XlChartTy pe.xlLine.xlLin e
Chart.SetSource Data(Sheet.Rang e("S1:S400"))

' Copy the chart to a PictureBox on this form, using the
clipboard.
Chart.ChartArea .Select()
Chart.ChartArea .Copy()

' Retrieve the picture.
Dim ChartImage As Image =
CType(Clipboard .GetDataObject( ).GetData(DataF ormats.Bitmap), Bitmap)
ChartImage.Save ("Test.jpg",
System.Drawing. Imaging.ImageFo rmat.Jpeg)

' Shrink the picture to fit the form.
Dim ScalingRatio As Single = PictureBox1.Wid th /
ChartImage.Widt h
ChartImage = ChartImage.GetT humbnailImage(C hartImage.Width *
ScalingRatio, ChartImage.Heig ht * ScalingRatio, Nothing, Nothing)

PictureBox1.Ima ge = ChartImage

' Modify the chart so it appears alongside the Excel table,
' instead of in a separate window.
' Chart.Location( Excel.XlChartLo cation.xlLocati onAsObject,
Sheet)
app.Workbooks.C lose()