Count rows between two range

[Cass]

Hi,
I can't count rows between range("A4") and "last used row" http://vbaexpress.com/forum/images/smilies/banghead.gif
now problem find last row http://vbaexpress.com/forum/images/smilies/doh.gif
' find last row?
lRow=Worksheets(4).Range("A4").End(xlDown).Row

[Bob Phillips]

Hi,
I can't count rows between range("A4") and "last used row" http://vbaexpress.com/forum/images/smilies/banghead.gif
now problem find last row http://vbaexpress.com/forum/images/smilies/doh.gif
' find last row?
lRow=Worksheets(4).Range("A4").End(xlDown).Row

Do you mean

Worksheets(4).Range("A4").End(xlDown).Row -3

[Cass]

Ok i resolve this but now another problem
I must count into 200 sheet
For i = 4 To 200
lRow = Sheet & i.Range("A4").End(xlDown).Row - 4
if counted=0 then
counted= lRow
else
counted=counted+lRow
end if
lrow = 0
Next i

Problem is Sheet & i. I cant use sheets code name!

[Killian]

Use the sheet indexSheets(i).Range("A4").End(xlDown).Row - 4

[Cass]

Use the sheet indexSheets(i).Range("A4").End(xlDown).Row - 4
ok but is Sheets(4) same as Sheet4?

[Killian]

Maybe not, Sheets(4) is the 4th sheet - "Sheet4" is the sheet you named "Sheet4", which could be in any position.
If your sheets are all named "Sheet1", "Sheet2" etc you can useSheets("Sheet" & i).Range("A4").End(xlDown).Row - 4

[Cass]

As you know is 2 different names sheets ;)

code name is sheets("Something") but if you watch vba project there are Sheet4 (Something)

I want use sheet4 ..... sheet200

[Cass]

"Sheet4" is the sheet you named "Sheet4", which could be in any position.

no the sheet4 isn't my named sheet. it's fourth created sheet and sheet200 is two-hundredth created sheet any position!

[johnske]

As you know is 2 different names sheets ;)

code name is sheets("Something") but if you watch vba project there are Sheet4 (Something)

I want use sheet4 ..... sheet200Almost, in the Project Explorer, Sheet4(Something) shows Sheet4 is the code-name and 'Something' is what someone has named it (the name on the sheet tab). Here's the three ways you can refer to the worksheetsSub SheetsName()

'activate the sheet by its position
'in the workbook (count from left)
'(can be very easily changed by user)
Sheets(1).Activate

'activate the sheet by using its given name (the
'one that's shown on the worksheet tab)
'(can be very easily changed by user)
Sheets("Sheet1").Activate

'activate the sheet by using its code-name (the
'name on the left in Project Explorer)
'(not easily changed - esp if project locked)
Sheet1.Activate
End SubIn this case you want to do something on every sheet except Sheet1, Sheet2, and Sheet3 (code-names). Use a For-Each-Next loop and Select Case to do this (it's also much faster than using an index number (i))...

Sub UseCodeName()
Dim Sheet As Worksheet
For Each Sheet In Worksheets
Select Case Sheet.CodeName
Case "Sheet1", "Sheet2", "Sheet3"
'Do nothing
Case Else
Sheet.Activate
'Do your thing here
End Select
Next Sheet
Set Sheet = Nothing
End Sub

[Cass]

but i have 200 sheet!!! how you think great so many select case?!
Maybe using array and collect sheets in array?! or count sheets ....:mkay

[johnske]

I thought you wanted to do something on 197 sheets and exclude the sheets with the code-names Sheet1, Sheet2, and Sheet3 from this action?

I so, you can't use an index variable such as 'i' with worksheet code-names, so you must do it another way, such as in the manner I've shown.

Put the code for what you want to do where I've written Sheet.Activate 'do your thing here' and that code will be applied to the 197 sheets.

I'm not entirely sure what you intend to do with lrow and Counted, but run this example of what you had for it above and view the result in the immediate window...Sub UseCodeName()
Dim Sheet As Worksheet, Counted As Long, lrow As Long
For Each Sheet In Worksheets
Select Case Sheet.CodeName
Case "Sheet1", "Sheet2", "Sheet3"
'Do nothing
Case Else
lrow = Range("A4").End(xlDown).Row - 4
If Counted = 0 Then
Counted = lrow
Else
Counted = Counted + lrow
End If
lrow = 0
Debug.Print Counted
End Select
Next Sheet
Set Sheet = Nothing
End Sub

[Cass]

ouch, yes in this way is it possible :thumb
test and try this way

---edit---
the lrow = Range("A4").End(xlDown).Row - 4
calculate activesheet rows not each sheet :think: