| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
last row in ANY column
Hi all,
I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Sub GetRealLastCell()
Dim RealLastRow As Long Dim RealLastColumn As Long Range("A1").Select On Error Resume Next RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row RealLastColumn = _ Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column Cells(RealLastRow, RealLastColumn).Select End Sub so you can use just this line: RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row -- Regards, Tom Ogilvy "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Dim LastRow as Long
LastRow = Cells.SpecialCells(xlLastCell).Row -- steveB Remove "AYN" from email to respond "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Many times gives inaccurate results for me.
-- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:Ckipe.12887$yS2.5191@trnddc07... Dim LastRow as Long LastRow = Cells.SpecialCells(xlLastCell).Row -- steveB Remove "AYN" from email to respond "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Tom,
Thanks! Forgot that Excel remembers the last cell even if all entries have been cleared. -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... Many times gives inaccurate results for me. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:Ckipe.12887$yS2.5191@trnddc07... Dim LastRow as Long LastRow = Cells.SpecialCells(xlLastCell).Row -- steveB Remove "AYN" from email to respond "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Thanks to both of you!
Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Stefi €žSTEVE BELL€ť ezt Ă*rta: Tom, Thanks! Forgot that Excel remembers the last cell even if all entries have been cleared. -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... Many times gives inaccurate results for me. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:Ckipe.12887$yS2.5191@trnddc07... Dim LastRow as Long LastRow = Cells.SpecialCells(xlLastCell).Row -- steveB Remove "AYN" from email to respond "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Hi Steffi,
Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Note Tom Ogilvy's comment: so you can use just this line: RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row The macro provided by Tom: Returns the last populated row Returns the last populated column Selects the last populated cell. Tom's oneliner would seem to provide a solution to your explicit request which is both simple (short) and consistently accurate. --- Regards, Norman "Stefi" wrote in message ... Thanks to both of you! Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Stefi "STEVE BELL" ezt írta: Tom, Thanks! Forgot that Excel remembers the last cell even if all entries have been cleared. -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... Many times gives inaccurate results for me. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:Ckipe.12887$yS2.5191@trnddc07... Dim LastRow as Long LastRow = Cells.SpecialCells(xlLastCell).Row -- steveB Remove "AYN" from email to respond "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Hi Norman,
Thanks for the explanation! May I ask you to explain further, why "*" is "The data to search for", and why [A1] the cell reference "after which you want the search to begin" (I mean why A1 is closed within brackets instead of "" as in other cases? Stefi €žNorman Jones€ť ezt Ă*rta: Hi Steffi, Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Note Tom Ogilvy's comment: so you can use just this line: RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row The macro provided by Tom: Returns the last populated row Returns the last populated column Selects the last populated cell. Tom's oneliner would seem to provide a solution to your explicit request which is both simple (short) and consistently accurate. --- Regards, Norman "Stefi" wrote in message ... Thanks to both of you! Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Stefi "STEVE BELL" ezt Ă*rta: Tom, Thanks! Forgot that Excel remembers the last cell even if all entries have been cleared. -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... Many times gives inaccurate results for me. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:Ckipe.12887$yS2.5191@trnddc07... Dim LastRow as Long LastRow = Cells.SpecialCells(xlLastCell).Row -- steveB Remove "AYN" from email to respond "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Hi Steffi,
Thanks for the explanation! May I ask you to explain further, why "*" is "The data to search for", and why [A1] the cell reference "after which you want the search to begin" (I mean why A1 is closed within brackets instead of "" as in other cases? Stefi The asterisk ( "*" ) is an Excel wildcard which represents any character or group of characters. It is used in Tom's code to search for any cell content. [A1] defines the same range as the conventional Range("A1") notation. It is normally more efficient and faster to use the 'long-hand' form, but in this case the square bracket notation is more concise and clear while the inefficiency overhead is totally insignificant. For more on square brackets, look at the 'Evaluate Method' in VBA Help. --- Regards, Norman "Stefi" wrote in message ... Hi Norman, Thanks for the explanation! May I ask you to explain further, why "*" is "The data to search for", and why [A1] the cell reference "after which you want the search to begin" (I mean why A1 is closed within brackets instead of "" as in other cases? Stefi "Norman Jones" ezt írta: Hi Steffi, Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Note Tom Ogilvy's comment: so you can use just this line: RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row The macro provided by Tom: Returns the last populated row Returns the last populated column Selects the last populated cell. Tom's oneliner would seem to provide a solution to your explicit request which is both simple (short) and consistently accurate. --- Regards, Norman "Stefi" wrote in message ... Thanks to both of you! Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Stefi "STEVE BELL" ezt írta: Tom, Thanks! Forgot that Excel remembers the last cell even if all entries have been cleared. -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... Many times gives inaccurate results for me. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:Ckipe.12887$yS2.5191@trnddc07... Dim LastRow as Long LastRow = Cells.SpecialCells(xlLastCell).Row -- steveB Remove "AYN" from email to respond "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Hi Norman,
Now it's clear, but what happens if one has to search just an asterisk as real content of cells? I saw somewhere ""*"" -like notation, would it work in this case? Regards Stefi €žNorman Jones€ť ezt Ă*rta: Hi Steffi, Thanks for the explanation! May I ask you to explain further, why "*" is "The data to search for", and why [A1] the cell reference "after which you want the search to begin" (I mean why A1 is closed within brackets instead of "" as in other cases? Stefi The asterisk ( "*" ) is an Excel wildcard which represents any character or group of characters. It is used in Tom's code to search for any cell content. [A1] defines the same range as the conventional Range("A1") notation. It is normally more efficient and faster to use the 'long-hand' form, but in this case the square bracket notation is more concise and clear while the inefficiency overhead is totally insignificant. For more on square brackets, look at the 'Evaluate Method' in VBA Help. --- Regards, Norman "Stefi" wrote in message ... Hi Norman, Thanks for the explanation! May I ask you to explain further, why "*" is "The data to search for", and why [A1] the cell reference "after which you want the search to begin" (I mean why A1 is closed within brackets instead of "" as in other cases? Stefi "Norman Jones" ezt Ă*rta: Hi Steffi, Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Note Tom Ogilvy's comment: so you can use just this line: RealLastRow = _ Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row The macro provided by Tom: Returns the last populated row Returns the last populated column Selects the last populated cell. Tom's oneliner would seem to provide a solution to your explicit request which is both simple (short) and consistently accurate. --- Regards, Norman "Stefi" wrote in message ... Thanks to both of you! Is it true that if it's certain that no cells were cleared after the last one, then Steve's simpler solution gives an accurate result? Stefi "STEVE BELL" ezt Ă*rta: Tom, Thanks! Forgot that Excel remembers the last cell even if all entries have been cleared. -- steveB Remove "AYN" from email to respond "Tom Ogilvy" wrote in message ... Many times gives inaccurate results for me. -- Regards, Tom Ogilvy "STEVE BELL" wrote in message news:Ckipe.12887$yS2.5191@trnddc07... Dim LastRow as Long LastRow = Cells.SpecialCells(xlLastCell).Row -- steveB Remove "AYN" from email to respond "Stefi" wrote in message ... Hi all, I found several tips for finding the last row containing data in a specific column. But what can I do for finding the last row containing not empty cells in any of the columns? Stefi |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Hi Steffi,
Now it's clear, but what happens if one has to search just an asterisk as real content of cells? I saw somewhere ""*"" -like notation, would it work in this case? In that case, simply use a preceding tilde(~) character which instructs Excel to trear the asterisk as a literal character rather than a wildcard. --- Regards, Norman |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
last row in ANY column
Thanks, now I have an answer to all my questions!
Regards, Stefi €žNorman Jones€ť ezt Ă*rta: Hi Steffi, Now it's clear, but what happens if one has to search just an asterisk as real content of cells? I saw somewhere ""*"" -like notation, would it work in this case? In that case, simply use a preceding tilde(~) character which instructs Excel to trear the asterisk as a literal character rather than a wildcard. --- Regards, Norman |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| divide column(x) by column(y) to give column(x/y) in excel? | New Users to Excel | |||
| Referencing date column A & time column B to get info from column | Excel Discussion (Misc queries) | |||
| Return text in Column A if Column B and Column K match | Excel Worksheet Functions | |||
| Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
| Divide Column A by Column B multiply Column C | Excel Worksheet Functions | |||
Linear Mode
