How do I create a range with all values in a sheet?
 

I have data in Excel format. It could be any number of rows and any number
of columns.

Can someone point me to an example of how to select all the data into a
range and iterate through the rows and columns?

TIA

David

How do I create a range with all values in a sheet?
 

"aualias" wrote in message
...
: I have data in Excel format. It could be any number of rows and any
number
: of columns.
:
: Can someone point me to an example of how to select all the data into a
: range and iterate through the rows and columns?
:
: TIA
:
: David

Here's one way (most likely not the best way to select your data, but you
get the idea)

Public Sub test()
Dim myRange As Range
Dim myCell As Range
Set myRange = UsedRange()
For Each myCell In myRange
Debug.Print myCell.Value
Next myCell
End Sub

Goes across rows first
Paul D

How do I create a range with all values in a sheet?
 

Paul,

My biggest problem is figuring out what the boundaries on the range should
be. I will not know in advance if the data has 2 columns or 30 columns, 10
rows or 1000.

Your function UsedRange() it the thing that I would like to know how to do.
How do I figure out the used range? How do I find the number of rows and
columns in the data?

Thanks.

David



"PaulD" <nospam wrote in message
...
"aualias" wrote in message
...
: I have data in Excel format. It could be any number of rows and any
number
: of columns.
:
: Can someone point me to an example of how to select all the data into a
: range and iterate through the rows and columns?
:
: TIA
:
: David

Here's one way (most likely not the best way to select your data, but you
get the idea)

Public Sub test()
Dim myRange As Range
Dim myCell As Range
Set myRange = UsedRange()
For Each myCell In myRange
Debug.Print myCell.Value
Next myCell
End Sub

Goes across rows first
Paul D

How do I create a range with all values in a sheet?
 

UsedRange is a built-in function that finds the "used range" on the
worksheet

msgbox UsedRange.Address

Somehow excel finds the first cell - defined by 1st row and 1st column
and than it finds the last cell - as found when you manually use Ctrl + End.
It includes all cells between the 1st and last even if some of the cells are
blank.

I used x = UsedRange.Address in a selection change event and kept getting
the correct
address of my used range, regarless of the cell I selected.

So you could use
Set rng = Range(UsedRange.Address)
to define the range and than
For each cel in rng
' do your thing
Next

hth
--
steveB

Remove "AYN" from email to respond
"aualias" wrote in message
...
Paul,

My biggest problem is figuring out what the boundaries on the range should
be. I will not know in advance if the data has 2 columns or 30 columns,
10 rows or 1000.

Your function UsedRange() it the thing that I would like to know how to
do. How do I figure out the used range? How do I find the number of rows
and columns in the data?

Thanks.

David



"PaulD" <nospam wrote in message
...
"aualias" wrote in message
...
: I have data in Excel format. It could be any number of rows and any
number
: of columns.
:
: Can someone point me to an example of how to select all the data into a
: range and iterate through the rows and columns?
:
: TIA
:
: David

Here's one way (most likely not the best way to select your data, but you
get the idea)

Public Sub test()
Dim myRange As Range
Dim myCell As Range
Set myRange = UsedRange()
For Each myCell In myRange
Debug.Print myCell.Value
Next myCell
End Sub

Goes across rows first
Paul D

How do I create a range with all values in a sheet?
 

Aaaaahh....

I looked up UsedRange as a function in the documentation since it was
written with (). Didn't think to look at the properties.

This looks like exactly what I need. I can get the column count with
aSheet.UsedRange.Columns.Count
the rows with
aSheet.UsedRange.Rows.Count
and access individual cells with
aSheet.UsedRange.Cells[i,j]

Thanks for the help.

David






"STEVE BELL" wrote in message
news:vfR2f.24808$3w.15579@trnddc07...
UsedRange is a built-in function that finds the "used range" on the
worksheet

msgbox UsedRange.Address

Somehow excel finds the first cell - defined by 1st row and 1st column
and than it finds the last cell - as found when you manually use Ctrl +
End.
It includes all cells between the 1st and last even if some of the cells
are blank.

I used x = UsedRange.Address in a selection change event and kept getting
the correct
address of my used range, regarless of the cell I selected.

So you could use
Set rng = Range(UsedRange.Address)
to define the range and than
For each cel in rng
' do your thing
Next

hth
--
steveB

Remove "AYN" from email to respond
"aualias" wrote in message
...
Paul,

My biggest problem is figuring out what the boundaries on the range
should be. I will not know in advance if the data has 2 columns or 30
columns, 10 rows or 1000.

Your function UsedRange() it the thing that I would like to know how to
do. How do I figure out the used range? How do I find the number of rows
and columns in the data?

Thanks.

David



"PaulD" <nospam wrote in message
...
"aualias" wrote in message
...
: I have data in Excel format. It could be any number of rows and any
number
: of columns.
:
: Can someone point me to an example of how to select all the data into
a
: range and iterate through the rows and columns?
:
: TIA
:
: David

Here's one way (most likely not the best way to select your data, but
you
get the idea)

Public Sub test()
Dim myRange As Range
Dim myCell As Range
Set myRange = UsedRange()
For Each myCell In myRange
Debug.Print myCell.Value
Next myCell
End Sub

Goes across rows first
Paul D

How do I create a range with all values in a sheet?
 

Ahh - the pleasure of a new and helpful tool!

You're very welcome... (but I can't take any credit)...

--
steveB

Remove "AYN" from email to respond
"aualias" wrote in message
...
Aaaaahh....

I looked up UsedRange as a function in the documentation since it was
written with (). Didn't think to look at the properties.

This looks like exactly what I need. I can get the column count with
aSheet.UsedRange.Columns.Count
the rows with
aSheet.UsedRange.Rows.Count
and access individual cells with
aSheet.UsedRange.Cells[i,j]

Thanks for the help.

David






"STEVE BELL" wrote in message
news:vfR2f.24808$3w.15579@trnddc07...
UsedRange is a built-in function that finds the "used range" on the
worksheet

msgbox UsedRange.Address

Somehow excel finds the first cell - defined by 1st row and 1st column
and than it finds the last cell - as found when you manually use Ctrl +
End.
It includes all cells between the 1st and last even if some of the cells
are blank.

I used x = UsedRange.Address in a selection change event and kept getting
the correct
address of my used range, regarless of the cell I selected.

So you could use
Set rng = Range(UsedRange.Address)
to define the range and than
For each cel in rng
' do your thing
Next

hth
--
steveB

Remove "AYN" from email to respond
"aualias" wrote in message
...
Paul,

My biggest problem is figuring out what the boundaries on the range
should be. I will not know in advance if the data has 2 columns or 30
columns, 10 rows or 1000.

Your function UsedRange() it the thing that I would like to know how to
do. How do I figure out the used range? How do I find the number of
rows and columns in the data?

Thanks.

David



"PaulD" <nospam wrote in message
...
"aualias" wrote in message
...
: I have data in Excel format. It could be any number of rows and any
number
: of columns.
:
: Can someone point me to an example of how to select all the data into
a
: range and iterate through the rows and columns?
:
: TIA
:
: David

Here's one way (most likely not the best way to select your data, but
you
get the idea)

Public Sub test()
Dim myRange As Range
Dim myCell As Range
Set myRange = UsedRange()
For Each myCell In myRange
Debug.Print myCell.Value
Next myCell
End Sub

Goes across rows first
Paul D