Count Rows Bug???

[Dave]

Hi,

I am having an issue when I try to counts rows in two separate workbooks. I
am not sure if this is a bug or me. Any assistance would be greatly
appreciated.

In workbook(1)Sheet(1), r is coming up with 1 row when there are actually 64
rows
In workbook(2)sheet(1) s is coming up with 55 rows which is correct.

r = Workbooks(1).Sheets(1).Range("A1").CurrentRegion.Rows.Count
s = Workbooks(2).Sheets(1).Range("A1").CurrentRegion.Rows.Count

I am not sure if I am providing enough information. This code used to work
but has just stopped working yesterday. We are using excel 2003.

 

[Patrick Molloy]

try explicitly naming the workbook and worksheets ... you can't always be
sure workbooks(1) referes to the correct book.
same with worksheets
in my test "sheet3" was actually sheet(1) so it returned 1 as well.

when opening workbooks you get better control using a workbook object

dim wb as workbook
set wb = workbooks.open({file})

r = wb.Sheets("sheet1").Range("A1").CurrentRegion.Rows.Count
wb.Close false ' closes without saving

 

[Dave Peterson]

Since you're relying on the .currentregion, I'm betting that you have a gap (an
empty row) in the middle of your data--so the currentregion isn't what you think
it is.

If you open that offending worksheet and select A1 and hit ctrl-* (or
F5|special|current region), what ends up getting selected?

 

[Jacob Skaria]

Just to add on to what Patrick mentioned..You can try out the below code from
which you can identify where it has gone wrong....or whether the current
region spans to only 1 row...

Msgbox Workbooks(1).Sheets(1).Range("A1").CurrentRegion.address
(External:=true)

If this post helps click Yes