Count Rows Bug???
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.R ows.Count s = Workbooks(2).Sheets(1).Range("A1").CurrentRegion.R ows.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. -- Thank you Dave |
Count Rows Bug???
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" wrote: 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.R ows.Count s = Workbooks(2).Sheets(1).Range("A1").CurrentRegion.R ows.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. -- Thank you Dave |
Count Rows Bug???
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? Dave wrote: 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.R ows.Count s = Workbooks(2).Sheets(1).Range("A1").CurrentRegion.R ows.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. -- Thank you Dave -- Dave Peterson |
Count Rows Bug???
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.a ddress (External:=true) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: 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.R ows.Count s = Workbooks(2).Sheets(1).Range("A1").CurrentRegion.R ows.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. -- Thank you Dave |
All times are GMT +1. The time now is 10:40 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com