newparadigmz

asked on

# Excel 2007 VBA, debugging range/array

For the following code;

Set rStartCell = ThisWorkbook.ActiveSheet.Range("A3")

Set rPathList = Range(rStartCell.Offset(0, 0), rStartCell.End(xlDown).Offset(0, 2))

iRows = rPathList.Rows.Count

iCols = rPathList.Columns.Count

ReDim avPaths(iRows, iCols)

avPaths = rPathList.Value

The range is n rows by 3 columns

This works as expected when n rows > 1

But when n rows is actually just 1 (because I'm deleteing all the data), iRows always = 4

Anyone see why?

Set rStartCell = ThisWorkbook.ActiveSheet.R

Set rPathList = Range(rStartCell.Offset(0,

iRows = rPathList.Rows.Count

iCols = rPathList.Columns.Count

ReDim avPaths(iRows, iCols)

avPaths = rPathList.Value

The range is n rows by 3 columns

This works as expected when n rows > 1

But when n rows is actually just 1 (because I'm deleteing all the data), iRows always = 4

Anyone see why?

Please post a sample workbook with the data where this fails.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

forgot about CurrentRegion

ASKER

If the range is > 1, rStartCell.End(xlDown), crawls down the range, BUT if range =1, rStartCell.End(xlDown) jumps to the next cell with data and counts everything in between (above it)

How do I prevent this so that if the range is 1 row, it equals 1 row, and not go to the next cell with data?