CODEWITHSUNDEEP
vb.netexcel
meltdownmonk
meltdownmonk

Reputation: 493

How can I limit the length of a row selection when using Excel.Range()

I am using VB.net code to manipulate Excel documents. I'm trying to grab a range of cells contained in a single specific row. I don't know the specific end cell, I just need All the cells that contain values in that row.

Here is a basic example of what i've been doing up to this point:

Dim rngRange as Excel.Range

Dim wksXLWorksheet As Excel.Worksheet

rngRange = wksXLWorksheet.Range("A1").EntireRow

I know that row containing the Range "A1" (which is a single cell) is the row that I want, and so I use the EntireRow property to return the entire row. It returns all the cells, including the unused ones, so over 16k in cells, most of them empty.

How can choose a range with only the used the Cells? I don't want to use UsedRange because that grabs everything, I only want the one row, and only up to where the cells no longer contain data.

Also, if i select a limited range like this, will it have more or less impact on resources? I was assuming it would have less impact selecting the small range, but I could be wrong.

Upvotes: 0

Views: 911

Answers (2)

user4039065
user4039065

Reputation:

The Excel.Application object has an Intersect method and the worksheet has a UsedRange property.

Dim rngRange as Excel.Range
Dim wksXLWorksheet As Excel.Worksheet
Dim xlApp As Excel.Application = New Microsoft.Office.Interop.Excel.Application()

rngRange = Excel.Intersect(wksXLWorksheet.Range("1:1"), wksXLWorksheet.UsedRange)

Alternately, define the range with the start and end points while qualifying the Cells that make up the Range.

Dim rngRange as Excel.Range
Dim wksXLWorksheet As Excel.Worksheet

rngRange = wksXLWorksheet.Range(wksXLWorksheet.Range("A1"), wksXLWorksheet.Range("XFD1").End(xlToLeft))

Upvotes: 0

pinebitter
pinebitter

Reputation: 100

Use .End property, e.g. Range("A1").End(xlToRight).

http://msdn.microsoft.com/en-us/library/office/ff839539.aspx

Upvotes: 1

Related Questions