UsedRange
 

Hi Y'all.

One of my subs clears the contents of a worksheet then pastes in new data. I
then concatenate the text in columns A & B in column H by going:
--------------
Set ur = ActiveSheet.UsedRange

For Each c In Range("A2:A" & ur.Rows.Count)
Cells(c.Row, 8) = Cells(c.Row, 1) & Cells(c.Row, 2)
Next c
--------------

The trouble is that there are 135 rows of data (the pasted data occupies
A1:G135), but ActiveSheet.UsedRange.Rows.Count only returns 134. Any ideas?
Sure I can add 1 but I'd like to know if I'm mis-using this property.

cheers

Damo

UsedRange
 

Are you sure that there's anything in row 1?

If your data were in A2:a135, then the ur.rows.count would be 134.

Usedrange doesn't always start in A1.

maybe:
dim LastRow as long
with activesheet.usedrange
LastRow = .rows(.rows.count).row
end with

then
For Each c In Range("A2:A" & ur.Rows.Count)
becomes
For Each c In Range("A2:A" & LastRow)

On the other hand, I like to just find a column that I know is always filled
(say column A). Then work from the bottom up:

dim Lastrow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
end with



Damien McBain wrote:

Hi Y'all.

One of my subs clears the contents of a worksheet then pastes in new data. I
then concatenate the text in columns A & B in column H by going:
--------------
Set ur = ActiveSheet.UsedRange

For Each c In Range("A2:A" & ur.Rows.Count)
Cells(c.Row, 8) = Cells(c.Row, 1) & Cells(c.Row, 2)
Next c
--------------

The trouble is that there are 135 rows of data (the pasted data occupies
A1:G135), but ActiveSheet.UsedRange.Rows.Count only returns 134. Any ideas?
Sure I can add 1 but I'd like to know if I'm mis-using this property.

cheers

Damo

--

Dave Peterson

UsedRange
 

Hi Damien

Try replacing.......

Set ur = ActiveSheet.UsedRange
For Each c In Range("A2:A" & ur.Rows.Count)

with

Set ur = Range(Range("A2"), _
Range("A" & Rows.Count).End(xlUp))
For Each c In ur


--
XL2002
Regards

William



"Damien McBain" wrote in message
...
| Hi Y'all.
|
| One of my subs clears the contents of a worksheet then pastes in new data.
I
| then concatenate the text in columns A & B in column H by going:
| --------------
| Set ur = ActiveSheet.UsedRange
|
| For Each c In Range("A2:A" & ur.Rows.Count)
| Cells(c.Row, 8) = Cells(c.Row, 1) & Cells(c.Row, 2)
| Next c
| --------------
|
| The trouble is that there are 135 rows of data (the pasted data occupies
| A1:G135), but ActiveSheet.UsedRange.Rows.Count only returns 134. Any
ideas?
| Sure I can add 1 but I'd like to know if I'm mis-using this property.
|
| cheers
|
| Damo
|
|

UsedRange
 

Spot on Dave, there was nothing in row 1. I incorrectly assumed that
UsedRange started at A1.

cheers & thanks,

Damo

Dave Peterson wrote:
Are you sure that there's anything in row 1?

If your data were in A2:a135, then the ur.rows.count would be 134.

Usedrange doesn't always start in A1.

maybe:
dim LastRow as long
with activesheet.usedrange
LastRow = .rows(.rows.count).row
end with

then
For Each c In Range("A2:A" & ur.Rows.Count)
becomes
For Each c In Range("A2:A" & LastRow)

On the other hand, I like to just find a column that I know is always
filled (say column A). Then work from the bottom up:

dim Lastrow as long
with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
end with



Damien McBain wrote:

Hi Y'all.

One of my subs clears the contents of a worksheet then pastes in new
data. I then concatenate the text in columns A & B in column H by
going: --------------
Set ur = ActiveSheet.UsedRange

For Each c In Range("A2:A" & ur.Rows.Count)
Cells(c.Row, 8) = Cells(c.Row, 1) & Cells(c.Row, 2)
Next c
--------------

The trouble is that there are 135 rows of data (the pasted data
occupies A1:G135), but ActiveSheet.UsedRange.Rows.Count only returns
134. Any ideas? Sure I can add 1 but I'd like to know if I'm
mis-using this property.

cheers

Damo