VB.Net / MS Excel - Select and Clear Range

Using Microsoft.Office.Interop.Excel.Application within my VB.Net application.

Trying to select a worksheet range ( A3: G LastRow ) and clear all in that range:

        Dim objExcel1 As New Microsoft.Office.Interop.Excel.Application
        objExcel1 = CreateObject("Excel.Application")
        objExcel1.Workbooks.Open("C:\Spreadsheet\Carbon.xlsx")
        objExcel1.Visible = False
        objExcel1.DisplayAlerts = False

        Dim CarbonWS As Worksheet, CarbonNewRng As Range
        CarbonWS = objExcel1.Sheets("CarbonScrap")
        CarbonNewRng = CarbonWS.Range(CarbonWS.Cells(3, 1), CarbonWS.Cells _(CarbonWS.Rows.Count, 7))
        CarbonNewRng.Clear()

        objExcel1.Workbooks(1).SaveAs("C:\Spreadsheet\Carbon.xlsx")
        objExcel1.Quit()
        objExcel1 = Nothing

Above doesn't work, what will?
Tim313Asked:
Who is Participating?
 
Tim313Connect With a Mentor Author Commented:
Thanks to all for your replies, but I found my own answer:

Dim CarbonWS As Worksheet
CarbonWS = objExcel1.Sheets("CarbonScrap")
CarbonWS.Range(CarbonWS.Cells(3, 1), CarbonWS.Cells(CarbonWS.Rows.Count, 7)).Clear()

Please note I stated:

"Trying to select a worksheet range ( A3: G LastRow ) and clear all in that range"

not just columns C:G.
0
 
Rgonzo1971Commented:
Hi,

Maybe CarbonNewRng.Clear instead of CarbonNewRng.Clear()

Regards
0
 
mvidasCommented:
Hi,

Your error looksl ike it is likely on this line:
CarbonNewRng = CarbonWS.Range(CarbonWS.Cells(3, 1), CarbonWS.Cells(CarbonWS.Rows.Count, 7))

Open in new window

It looks like you're just clearing columns C:G, so you can instead use:
CarbonNewRng = CarbonWS.Columns("C:G")

Open in new window


You shouldn't need that entire block though. Instead of making the worksheet and range variables, just use the single line:
objExcel1.Sheets("CarbonScrap").Columns("C:G").Clear()

Open in new window

Should take care of it for you.
You may need to add .ActiveWorkbook in there:
objExcel1.ActiveWorkbook.Sheets("CarbonScrap").Columns("C:G").Clear()
Matt
0
 
Tim313Author Commented:
Found the solution on my own. Suggestions provided by others would not work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.