VBA for selecting a range of cells based on their value

[danb96]

Hello, I am new to this forum so apologies if this is in the wrong place.

I'm writing a VBA code to open an Excel file and create a scatter graph using only two columns, which is pretty simple. However, I'd like to know how to select only the cells that have a value. For example, the excel sheet may be empty apart from data between S112:T7800. If I select the whole of both columns (S1:T1400000), the graph displays a lot of blanks spaces (as well as being inefficient code!). The data array will always be in column S to T, but the rows at which the array starts and finishes varies.

Any help would be appreciated as I'm at a bit of a loss.

Thanks in advance,
Dan

 

[Michael M]

Maybe this way

Sub MM1()

    Dim fRow As Long, lRow As Long, myRange As Range
    If Cells(1, "S") <> "" Then
        fRow = 1
    Else
        fRow = Cells(1, "S").End(xlDown).Row
    End If
    lRow = Cells(Rows.Count, "T").End(xlUp).Row
    If lRow >= fRow Then
        Set myRange = Range(Cells(fRow, "S"), Cells(lRow, "T"))
    End If
   myRange.Select
End Sub

 

[danb96]

Michael M,

Thanks very much for your reply. I think because of the way my S and T columns were copied, unfortunately the code didn't work. However, it gave me the idea of putting unused cells as #N/A, which solved my problem. Thanks again,

Dan

 

[Michael M]

Ok....as long as you got the required result..