Syntax to select all data
 

Let me start off by saying I have some programming experience, but am very
new to VBA.
I want to create a macro that will select a range of data, sort it, insert
new lines, and take the sum of the parts. Where I'm hung up on, is that the
data won't always be the same length. I know it will always be the same
width, so the column is not a problem, but how would I go about finding the
bottom-right cell of data? I believe I know how to find the last ROW of
data, but not the specific cell.

Syntax to select all data
 

If the data is contiguous starting in A1

set rng = range("A1").CurrentRegion
msgbox rng(rng.count).Address

--
Regards,
Tom Ogilvy


"Stephen" wrote in message
...
Let me start off by saying I have some programming experience, but am very
new to VBA.
I want to create a macro that will select a range of data, sort it, insert
new lines, and take the sum of the parts. Where I'm hung up on, is that
the
data won't always be the same length. I know it will always be the same
width, so the column is not a problem, but how would I go about finding
the
bottom-right cell of data? I believe I know how to find the last ROW of
data, but not the specific cell.

Syntax to select all data
 

Activesheet.range("A:A").End(XLDown)

Returns the range that is the last "used" cell in Column A


"Stephen" wrote:

Let me start off by saying I have some programming experience, but am very
new to VBA.
I want to create a macro that will select a range of data, sort it, insert
new lines, and take the sum of the parts. Where I'm hung up on, is that the
data won't always be the same length. I know it will always be the same
width, so the column is not a problem, but how would I go about finding the
bottom-right cell of data? I believe I know how to find the last ROW of
data, but not the specific cell.

Syntax to select all data
 

Perhaps I am not being clear enough. The data I am referring to will be
import from another sources and not entered manually. I don't know if the
above code will help. I believe this will get me the last row of data:

ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End (xlUp)

but how can I combine that with that last column of data? (It will be column
'H')

Syntax to select all data
 

If you didn't have any empty columns or empty rows in that range, Tom's
suggestion may have worked nicely.

But...

dim LastRow as long
dim myRng as range

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("a1:H" & Lastrow)
end with

It still uses column A to find the last used row, though.

Stephen wrote:

Perhaps I am not being clear enough. The data I am referring to will be
import from another sources and not entered manually. I don't know if the
above code will help. I believe this will get me the last row of data:

ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End (xlUp)

but how can I combine that with that last column of data? (It will be column
'H')

--

Dave Peterson

Syntax to select all data
 

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

"Dave Peterson" wrote:

If you didn't have any empty columns or empty rows in that range, Tom's
suggestion may have worked nicely.

But...

dim LastRow as long
dim myRng as range

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("a1:H" & Lastrow)
end with

It still uses column A to find the last used row, though.

Stephen wrote:

Perhaps I am not being clear enough. The data I am referring to will be
import from another sources and not entered manually. I don't know if the
above code will help. I believe this will get me the last row of data:

ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End (xlUp)

but how can I combine that with that last column of data? (It will be column
'H')

--

Dave Peterson

Syntax to select all data
 

Ah, but that can be different from what you think it is. It's often way past
what you may think the last cell really is.

Vacation's Over wrote:

ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

"Dave Peterson" wrote:

If you didn't have any empty columns or empty rows in that range, Tom's
suggestion may have worked nicely.

But...

dim LastRow as long
dim myRng as range

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("a1:H" & Lastrow)
end with

It still uses column A to find the last used row, though.

Stephen wrote:

Perhaps I am not being clear enough. The data I am referring to will be
import from another sources and not entered manually. I don't know if the
above code will help. I believe this will get me the last row of data:

ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End (xlUp)

but how can I combine that with that last column of data? (It will be column
'H')

--

Dave Peterson


--

Dave Peterson