Rows.Count Method is not working well in Excel VBA(It gets rows number in wrong sheet although I specified what sheet to get numbers of rows.)

Question

Sub Example()


On Error GoTo ErrHandler
    Application.ScreenUpdating = False

    Dim src As Workbook

    '//Open The Source Excel Workbook In "Read-Only-Mode"
    Set src = Workbooks.Open(Cells(103, 103).Value, True, True)
    'Set src = Workbooks.Open("E:\Example\Example\Example.xlsx", True, True)

    '//Get The Total Rows From The Source Workbook
    Dim iTotalRows As Long

    iTotalRows = src.Worksheets("Example").Range("B1:B" & Cells(src.Worksheets("Example").Rows.Count, "B").End(xlUp).Row).Rows.Count 
    MsgBox iTotalRows

    Dim iCnt As Long
    Dim 행 As Integer
    .......

Above is my code.

I apparently specified the sheet's name in below code.

iTotalRows = src.Worksheets("Example").Range("B1:B" & Cells(src.Worksheets("Example").Rows.Count, "B").End(xlUp).Row).Rows.Count

but it returns abnormal result.

What I want to know is "Example" Sheet's rows.

Usually it returns "Example" Sheet's rows well.

because "Example" Sheet is last-opened sheet of the workbook.

When I open another sheet and close and save the workbook, Excel vba code Count that sheet's rows!

I specified apparently sheet's name, but it seems to have no effect.

How Do I solve this problem?

Thank you for your answer in advance.

score 2 · Accepted Answer · answered Jan 01 '18 at 04:46

2

The cells has no defined parent.

iTotalRows = src.Worksheets("Example").Range("B1:B" & src.Worksheets("Example").Cells(src.Worksheets("Example").Rows.Count, "B").End(xlUp).Row).Rows.Count

Since you are starting in the first row (e.g. B1), this might be better as,

with src.Worksheets("Example")
    iTotalRows = .cells(.rows.count, "B").end(xlup).row
end with
MsgBox iTotalRows

answered Jan 01 '18 at 04:46

You may be interested in [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells). – Jan 01 '18 at 04:55
1

Thank you, My code still have problem, but one problem is solved thanks to you. Your code is much simpler and easy and smart. Thank you. – 박영종 Jan 01 '18 at 04:57
The `with ... end with` guarantees that you are not referencing the default worksheet. – Jan 01 '18 at 04:57

Rows.Count Method is not working well in Excel VBA(It gets rows number in wrong sheet although I specified what sheet to get numbers of rows.)

1 Answers1