Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB.Net / MS Excel - Select and Clear Range

Posted on 2014-02-07
4
Medium Priority
?
4,606 Views
Last Modified: 2014-02-12
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?
0
Comment
Question by:Tim313
  • 2
4 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39842150
Hi,

Maybe CarbonNewRng.Clear instead of CarbonNewRng.Clear()

Regards
0
 
LVL 35

Expert Comment

by:mvidas
ID: 39842152
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
 

Accepted Solution

by:
Tim313 earned 0 total points
ID: 39842599
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
 

Author Closing Comment

by:Tim313
ID: 39852806
Found the solution on my own. Suggestions provided by others would not work.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question