Clearing a range of cell in Excel from Access VBA

[dccjr]

OK, I have numerous ways to do this from within Excel VBA, but nothing that works from Access VBA. I can clear the entire sheet, but that is not what I want to do. I only want to clear A2:B35. This is what I have that clears the entire sheet. I know there is a way to do this, but can't find an example.

Function ClearTrendData()
Dim oXL As Object
Dim oBook As Object
Dim oSheet As Object

Set oXL = CreateObject("Excel.Application")
On Error Resume Next

oXL.UserControl = True
On Error GoTo 0
On Error GoTo ErrHandle

Set oBook = oXL.Workbooks.Open("E:\RCADatabase(FE) Current\RCACharting.xlsm")
Set oSheet = oBook.Sheets("TrendData")

oSheet.Cells.Clear
oBook.Save

ErrExit:
       oBook.Close
       oXL.Application.Quit
       Set oXL = Nothing
       Set oBook = Nothing
       Set oSheet = Nothing
       Exit Function
ErrHandle:
      oXL.Visible = False
      MsgBox Err.Description
      GoTo ErrExit
End Function

I am sure that it is a change to the blue lines. Any suggestions?

[alansidman]

Try this:

oSheet.Range("A2:B35").Clear

Alan

[dccjr]

My apologies, I have omitted one thing. I need to clear a dynamic range, as it changes based on the query used and the number of records.

[alansidman]

You will need to be more specific in your requirements. More details needed to clearly understand the issues.

[dccjr]

I use a table to export to Excel for charting purposes. Each time the user runs a new chart, I typically clear the sheet with the code above before exporting from Access. The data is from a COUNT statement, so it is always two columns, but depending on the Category selected, can have anywhere from 6 - 40 rows. Clearing the sheet causes a problem because I format the data sheet for the chart. The second problem is the I am thinking I can use the same type of dynamic selection code to only chart the rows with information. I currently have the data range fixed, but this gives empty columns on the chart.

I guess what I am looking for is a what to dynamically select a range of cells with VBA code.