| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What does this do?
I got some help here writing some code. I'm trying to do something very
similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
In the code, Rows.Count returns the number of rows in the
worksheet, or 65536. Therefore, the ..Cells(Rows.Count,2) refers to the last cell in column 2. Then, the .End(xlUp) causes Excel to scan upwards until a non-empty cell is found. The .Row property returns the row number of that last cell. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "davegb" wrote in message ups.com... I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
the rows.ccount returns the number of rows in the spreadsheet (65,536). The
..end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ ..End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
While the need is dissipating, in xl5/95, you choice would error. That is
why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
Thanks Tom. I didn't know that. My programming started after that (obviously).
-- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
And you can use
Cells(Rows.Count,"B") as you now know :-) Bob "Jim Thomlinson" wrote in message ... Thanks Tom. I didn't know that. My programming started after that (obviously). -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
I am off to get some coffee. This is more Monday than I am prepared for... :-)
-- HTH... Jim Thomlinson "Bob Phillips" wrote: And you can use Cells(Rows.Count,"B") as you now know :-) Bob "Jim Thomlinson" wrote in message ... Thanks Tom. I didn't know that. My programming started after that (obviously). -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
Tom Ogilvy wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! thanks to everyone for your prompt and helpful replies! Your explanations beg the question, why count up from the bottom instead of down from the data itself? Is this to handle the potential for blank cells in that column? If so, then if there were blanks in the bottom rows in this column, you'd get a miscount anyway. Is there some other reason for counting up from the bottom of the sheet? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
why count up from the bottom instead of
down from the data itself? Is this to handle the potential for blank cells in that column? If the data contains embedded blank cells, the End(xlDown) method will not take you to the last cell. It will take you to the cell above the first blank cell. Going from the bottom up will always take you to the last cell with data. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "davegb" wrote in message ups.com... Tom Ogilvy wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! thanks to everyone for your prompt and helpful replies! Your explanations beg the question, why count up from the bottom instead of down from the data itself? Is this to handle the potential for blank cells in that column? If so, then if there were blanks in the bottom rows in this column, you'd get a miscount anyway. Is there some other reason for counting up from the bottom of the sheet? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
Chip Pearson wrote: why count up from the bottom instead of down from the data itself? Is this to handle the potential for blank cells in that column? If the data contains embedded blank cells, the End(xlDown) method will not take you to the last cell. It will take you to the cell above the first blank cell. Going from the bottom up will always take you to the last cell with data. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com Thanks! "davegb" wrote in message ups.com... Tom Ogilvy wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! thanks to everyone for your prompt and helpful replies! Your explanations beg the question, why count up from the bottom instead of down from the data itself? Is this to handle the potential for blank cells in that column? If so, then if there were blanks in the bottom rows in this column, you'd get a miscount anyway. Is there some other reason for counting up from the bottom of the sheet? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
Out of shear curiosity how many rows does 95 have? It is around 16k isn't it?
-- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
It is indeed 16K rows.
-- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Jim Thomlinson" wrote in message ... Out of shear curiosity how many rows does 95 have? It is around 16k isn't it? -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
Yep, 2^14
-- HTH RP (remove nothere from the email address if mailing direct) "Jim Thomlinson" wrote in message ... Out of shear curiosity how many rows does 95 have? It is around 16k isn't it? -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
16,784
-- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... Out of shear curiosity how many rows does 95 have? It is around 16k isn't it? -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
2^14 = 16,384
So which is it? 16,384 or 16,784? I am guessing 16,384 since it is a power of 2, but stranger things have happened. -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: 16,784 -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... Out of shear curiosity how many rows does 95 have? It is around 16k isn't it? -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
What does this do?
16384 not 16784 I think you mean.
-- HTH RP (remove nothere from the email address if mailing direct) "Tom Ogilvy" wrote in message ... 16,784 -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... Out of shear curiosity how many rows does 95 have? It is around 16k isn't it? -- HTH... Jim Thomlinson "Tom Ogilvy" wrote: While the need is dissipating, in xl5/95, you choice would error. That is why most use Rows.count which would work in all situations. -- Regards, Tom Ogilvy "Jim Thomlinson" wrote in message ... the rows.ccount returns the number of rows in the spreadsheet (65,536). The .end(xlUp) travels up from this row to the first non-blank cell. Personally (and this is a personal preference) I would write that line like this... lEndRow = wbkNewHdr.Sheets("Macro Records").Range("B65536") _ .End(xlUp).Row because in my opionion Range("B65536") is far more clear to read than Cells(Rows.Count, 2) -- HTH... Jim Thomlinson "davegb" wrote: I got some help here writing some code. I'm trying to do something very similar to what this line does, but I don't understand this line. lEndRow = wbkNewHdr.Sheets("Macro Records").Cells(Rows.Count, 2) _ .End(xlUp).Row I know it defines the final row in the named worksheet. What I don't understand is what the .Count does, or why the xlUp is not a xlDown. Can someone explain this line for me? Thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Hybrid Mode
