Value of a range of cells in Excel ???

[wengang]

Hi all.

I just need one line for VB. It is just to say if any of the cells in a range have data in them, then.....


If (Range of Cells).Value <> "" Then.........

But this isn't right. Is there one line in VBA that means the value of any of the cells?


Thanks

Wengang

[michelle]

Hello wengang,

Try this source:

Private Sub Form_Load()
Dim XLS As Object
Dim XLSheet As Object

' Set the Application (Excel)
Set XLS = CreateObject("Excel.Application")

Set XLSheet = XLS.Worksheets("YourSheet")

If XLSheet.Cells(1, 2) <> "" Then
End If

End Sub

Nice regards,

Michelle.

[Sforster]

You could also use this if you wanted a whole range....

Dim Xlobj as object, Xlsheet as object
Dim IsThisCellEmpty as variant

Set Xlobj = CreateObject("Excel.Application")
Xlobj.Workbooks.open("C:\MyWorkbook To Check")
Set Xlsheet = Xlobj.Activeworkbook.Sheets("MySheet To Check")

With Xlsheet
  For Each IsThisCellEmpty In .Range("A2:A9")
      If IsThisCellEmpty.Value <> "" Then
          Debug.Print IsThisCellEmpty.Address & " Isn't Empty and Has The Value " & IsThisCellEmpty.Value
      End If
  Next IsThisCellEmpty
End With

Where you can change the range A2:A9 to whatever range you want. Hope this helps

Cheers
Steve