End property
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 |
End property
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 |
End property
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 |
End property
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 |
End property
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 |
End property
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 |
End property
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 |
End property
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 |
End property
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 |
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 |
End property
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 |
All times are GMT +1. The time now is 11:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com