Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SpecialCells and UsedRange | Excel Programming | |||
usedrange doesn't show the right value | Excel Programming | |||
UsedRange problem | Excel Programming | |||
UsedRange problem | Excel Programming | |||
Usedrange | Excel Programming |