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

Question

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.

pinebitter · Accepted Answer · 2012-08-27 14:45:43Z

1

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

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

answered Aug 27, 2012 at 14:45

pinebitter

10011 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

meltdownmonk Over a year ago

Thanks, that was simple. I had looked at the end property in some online documentation but I ended up skipping over it when it didn't work the way I expected; that was before I got to my current understanding of ranges.. Thanks!

Scott Holtzman Over a year ago

@meltdownmonk -> While this is a good answer, a better solution might be this: rngRange = wksXLWorksheet.Cells(1, wksXLWorksheet.Range("A" & wksXLWorksheet.Columns.Count).End(xlToLeft).Column) This syntax moves from the last column to the left to find the real row end, were as the starting from cell A1 and moving to the right xlToRight will stop at the first random blank cell that shows up, should there be any.

meltdownmonk Over a year ago

I don't think this completely answered the question... This only returns the last used cell in the row, not all the cells.

user4039065 · Accepted Answer · 2017-06-16 02:07:16Z

0

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))

answered Jun 16, 2017 at 2:07

user4039065

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related