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

Hello Stefi
Use
Range("A65536").End(xlUp).Row

HTH
Cordially
Pascal

"Stefi" a écrit dans le message de news:
...
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

Go up from the bottom instead:
cells(rows.count,"a").end(xlup).row

"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

Try:-

LastRow = Range("A65536").End(xlUp).Row


Mike

"Stefi" wrote:

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

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

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

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

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

I forgot to mention that on my screen Worksheets(Target.Value) WAS selected,
and I was deceived by that.
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

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

Thanks Dave for your explanation, it's clear now. I learned something again.
Stefi


€žDave Peterson€� ezt Ã*rta:

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