Error with Selection.CurrentRegion.Rows.Count?

[KenY]

Hi
I have previous experience of consolidating several worksheets into a single
worksheet and my method has worked well.
Typically, the method is:

'select a cell in the table, take a row count and copy it
Cells(5, 5).Select
Selection.CurrentRegion.Select
add_rows_cntr = Selection.CurrentRegion.Rows.Count
Selection.Copy

' paste the data values back into the consolidation
' check that there is space in the consolidation worksheet
If curr_rows_cntr + add_rows_cntr > 65536 Then MsgBox "Spreadsheet
Overflow": GoTo abort_consolid_loop
Windows("CAT_Consolidation.xls").Activate
Sheets("All_CAT_Data").Activate
' select the first empty row in the consolidation worksheet and paste the
data
Cells(curr_rows_cntr + 1, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

So far, so good. Except I now have a scenario where the row count of my
current region is returned=1, but the copied area is 31 rows long. When I
check things manually in the source worksheet, using the cntl* command to
identify the current region (having selected the same cell as in macro), the
full 31 row area is selected.

The cause of the difference seems to be where the blank cells lie within my
'CurrentRegion', but it bothers me that the Excel behaviour is not the same
for the statements
' add_rows_cntr = Selection.CurrentRegion.Rows.Count' and
' Selection.CurrentRegion.Select
Selection.Copy'

Has anyone come across this and found a solution?

Data area format that throws the problem (hope this shows up ok in the post):

Value1 <blank> <blank> <blank>
<blank> <blank> <blank> Head1
<blank> Head2 Head3 Head4
head5 Head6 Head7 Head8
Value2 <blank> Value3 Value4
Value5 Value6 Value7 <blank>

If I make sure that the first column is continuous, when I select head5 as
my active cell for and ask for the CurrentRegion, I get the result I want.
As I said, I am more concerned that I have found an inconsistent behaviour
(Excel 2003) rather than wanting to know how I can fix my table.

 

[Joel]

I usually do something like this

LastRow = 0
LastCol = 10
For ColCount = 1 to 10
LRow = cells(Rows.Count).End(xlup).Row
if LRow > LastRow then
LastRow = LRow
end if
next i

 

[Mohan Ram]

Just finiding difficult in knowing what is below code is useful for it wld be great if some one explians with examples

Selection.CurrentRegion.Rows.Count - 1



Joe wrote:

I usually do something like thisLastRow = 0LastCol = 10For ColCount = 1 to 10
11-Nov-08

I usually do something like this

LastRow = 0
LastCol = 10
For ColCount = 1 to 10
LRow = cells(Rows.Count).End(xlup).Row
if LRow > LastRow then
LastRow = LRow
end if
next i


:

Previous Posts In This Thread:

Error with Selection.CurrentRegion.Rows.Count?
Hi
I have previous experience of consolidating several worksheets into a single
worksheet and my method has worked well.
Typically, the method is:

'select a cell in the table, take a row count and copy it
Cells(5, 5).Select
Selection.CurrentRegion.Select
add_rows_cntr = Selection.CurrentRegion.Rows.Count
Selection.Copy

' paste the data values back into the consolidation
' check that there is space in the consolidation worksheet
If curr_rows_cntr + add_rows_cntr > 65536 Then MsgBox "Spreadsheet
Overflow": GoTo abort_consolid_loop
Windows("CAT_Consolidation.xls").Activate
Sheets("All_CAT_Data").Activate
' select the first empty row in the consolidation worksheet and paste the
data
Cells(curr_rows_cntr + 1, 4).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

So far, so good. Except I now have a scenario where the row count of my
current region is returned=1, but the copied area is 31 rows long. When I
check things manually in the source worksheet, using the cntl* command to
identify the current region (having selected the same cell as in macro), the
full 31 row area is selected.

The cause of the difference seems to be where the blank cells lie within my
'CurrentRegion', but it bothers me that the Excel behaviour is not the same
for the statements
' add_rows_cntr = Selection.CurrentRegion.Rows.Count' and
' Selection.CurrentRegion.Select
Selection.Copy'

Has anyone come across this and found a solution?

Data area format that throws the problem (hope this shows up ok in the post):

Value1 <blank> <blank> <blank>
<blank> <blank> <blank> Head1
<blank> Head2 Head3 Head4
head5 Head6 Head7 Head8
Value2 <blank> Value3 Value4
Value5 Value6 Value7 <blank>

If I make sure that the first column is continuous, when I select head5 as
my active cell for and ask for the CurrentRegion, I get the result I want.
As I said, I am more concerned that I have found an inconsistent behaviour
(Excel 2003) rather than wanting to know how I can fix my table.
--
KenY

I usually do something like thisLastRow = 0LastCol = 10For ColCount = 1 to 10
I usually do something like this

LastRow = 0
LastCol = 10
For ColCount = 1 to 10
LRow = cells(Rows.Count).End(xlup).Row
if LRow > LastRow then
LastRow = LRow
end if
next i


:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Crypto Obfuscator for .NET - Product Review
http://www.eggheadcafe.com/tutorial...f8-f5fd987fafb1/crypto-obfuscator-for-ne.aspx