End property
If you use unqualified ranges in your code, then excel behaves differently
depending on where the code is located.
If the code is in a General module, then the unqualified range will refer to the
active sheet.
If the code is in a worksheet module, then the unqualified range will refer to
the sheet that owns the code.
For instance:
worksheets("Sheet1").select
msgbox range("a1").value
will show the value of sheet1!a1 if the code is in a general module.
It will show the value of A1 of whatever sheet the code is in if that code is in
a worksheet module.
I find it better to always qualify the ranges.
with worksheets("sheet1")
msgbox .range("a1").value
end with
=====
or in your case:
with worksheets(target.value)
kovsor = .Range("A" & .Rows.Count).End(xlUp).Row + 1
...
End with
Stefi wrote:
Problem is solved, even if I don't clearly understand why.
I used
Range("A" & Rows.Count).End(xlUp).Row
in a Worksheet_Change event macro like that:
Worksheets(Target.Value).Select
kovsor = Range("A" & Rows.Count).End(xlUp).Row + 1
so the appropriate worksheet was selected, but Excel didn't refer to
Worksheets(Target.Value) but to Activesheet. When I changed to
kovsor = Worksheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Row + 1
I got the right result.
It seems that in a Change event selecting another worksheet has no effect.
Thanks to all of you!
Stefi
Dave Peterson ezt *rta:
If you put
=counta(a:a)
in an empty cell, what's returned?
Stefi wrote:
Thanks guys to all of you, it's nice, but the my problem remains:
Range("A" & Rows.Count).End(xlUp).Row
ALWAYS gives 1. I'd like a formula giving 1 when I have only header row, 2
when header row and one data row, etc.
I found that
Columns("A:A").Find(What:="*", _
After:=Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
works this way, but I thought that I can get the same result with the End
property in a simpler way. Was I wrong?
Regards,
Stefi
âžBob Phillipsâ ezt Ã*rta:
LastRow = Range("A" & Rows.Count).End(xlUp).Row
--
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Stefi" wrote in message
...
Hi All,
I wanted to use
Columns("A:A").End(xlDown).Row
to determine last cell in column A. Column A has a header in A1.
Columns("A:A").End(xlDown).Row gives the correct result if I have at least
one not blank cell (A2) in column A, but gives 65536 if I have only the
header. I'd expect 1 as result.
Is there a simple way of getting 1 in this case, other then complicated IF
structures?
Thanks,
Stefi
--
Dave Peterson
--
Dave Peterson
|