CODEWITHSUNDEEP
vbaexcel
Misha AM
Misha AM

Reputation: 137

Counting the number of rows in a specific worksheet

I launch the following code from workbook1, and want to count the number of rows in workbook2(wb.Name). Why does partII return the row count from workbook1? 

Dim partII As String
Workbooks(wb.Name).Activate
Workbooks(wb.Name).Worksheets("sheet1").Select
partII = Range("A" & Rows.Count).End(xlUp).Row
For Each myRecord In Workbooks(wb.Name).Worksheets("sheet1").Range("A1:A" & partII)

Upvotes: 2

Views: 93

Answers (1)

nutsch
nutsch

Reputation: 5962

  1. Avoid Activate and Select as much as possible
  2. I repeat, avoid Activate and Select as much as possible
  3. Your range refers to the active sheet, so it should be pulling wb.name / sheet 1.

Try the following simplified code:

with wb.worksheets("sheet1")
    partII = .Range("A" & .Rows.Count).End(xlUp).Row 

    For Each myRecord In .Range("A1:A" & partII)
        '...
    Next myRecord

end with

Upvotes: 1

Related Questions