Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find maximum row count of data
How would I find the last (bottom row) of data when data may be missing in
some columns? I'm trying to set Chart options on the run with macros instead of setting the bottom row to a default value such as row 1000. The number of rows is changing depending on options picked from a ListBox. Thanks Steve |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find maximum row count of data
Do you mean by using a worksheet formula? If so, do you have a column that
will always have data in the last row? If so, give this formula a try (where I have assumed that column with data in the last row is A)... =MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0) Rick "Steve" wrote in message ... How would I find the last (bottom row) of data when data may be missing in some columns? I'm trying to set Chart options on the run with macros instead of setting the bottom row to a default value such as row 1000. The number of rows is changing depending on options picked from a ListBox. Thanks Steve |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find maximum row count of data
No Rick
I'm looking for a VB or macro solution so that I can change chart ranges on the fly. "Rick Rothstein (MVP - VB)" wrote: Do you mean by using a worksheet formula? If so, do you have a column that will always have data in the last row? If so, give this formula a try (where I have assumed that column with data in the last row is A)... =MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0) Rick "Steve" wrote in message ... How would I find the last (bottom row) of data when data may be missing in some columns? I'm trying to set Chart options on the run with macros instead of setting the bottom row to a default value such as row 1000. The number of rows is changing depending on options picked from a ListBox. Thanks Steve |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find maximum row count of data
Again, if you have a column that will always have data in the last row of
your data, then you could do this (again assuming that column is A)... LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row If, on the other hand, there is no row always guaranteed to have data in it for the last row of data, then you can use this function to get the highest row number that contains data... Function MaxRowInUse(Optional WS As Worksheet, Optional _ FactorInHiddenRows As Boolean = False) As Long Dim X As Long Dim LastRow As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Columns.Count If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then LastRow = .Cells(.Rows.Count, X).End(xlUp).Row If LastRow MaxRowInUse Then MaxRowInUse = LastRow End If Next End With End Function Note: If you don't specify a worksheet in the first (optional) argument, then the active sheet is used. The second optional argument is the interesting one... it lets you determine whether to include hidden rows or columns when determining the maximum row and column that are in use; that is, if a hidden row or column contains the maximum row or column, it will be ignored unless the second argument is set to True. This allows you to get the maximum row or column for what you see on the worksheet rather than for what what any hidden data would return. I wasn't sure which would be the most logical default for this second argument, so I chose not factor in hidden rows or columns (that is, the functions return the maximum row and column for only the visible data); if desired, this can be easily changed in the declaration headers for each function. Rick "Steve" wrote in message ... No Rick I'm looking for a VB or macro solution so that I can change chart ranges on the fly. "Rick Rothstein (MVP - VB)" wrote: Do you mean by using a worksheet formula? If so, do you have a column that will always have data in the last row? If so, give this formula a try (where I have assumed that column with data in the last row is A)... =MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0) Rick "Steve" wrote in message ... How would I find the last (bottom row) of data when data may be missing in some columns? I'm trying to set Chart options on the run with macros instead of setting the bottom row to a default value such as row 1000. The number of rows is changing depending on options picked from a ListBox. Thanks Steve |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find maximum row count of data
Thanks so much Rick. That should do it.
"Rick Rothstein (MVP - VB)" wrote: Again, if you have a column that will always have data in the last row of your data, then you could do this (again assuming that column is A)... LastRow = Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row If, on the other hand, there is no row always guaranteed to have data in it for the last row of data, then you can use this function to get the highest row number that contains data... Function MaxRowInUse(Optional WS As Worksheet, Optional _ FactorInHiddenRows As Boolean = False) As Long Dim X As Long Dim LastRow As Long If WS Is Nothing Then Set WS = ActiveSheet With WS For X = 1 To .UsedRange.Columns.Count If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then LastRow = .Cells(.Rows.Count, X).End(xlUp).Row If LastRow MaxRowInUse Then MaxRowInUse = LastRow End If Next End With End Function Note: If you don't specify a worksheet in the first (optional) argument, then the active sheet is used. The second optional argument is the interesting one... it lets you determine whether to include hidden rows or columns when determining the maximum row and column that are in use; that is, if a hidden row or column contains the maximum row or column, it will be ignored unless the second argument is set to True. This allows you to get the maximum row or column for what you see on the worksheet rather than for what what any hidden data would return. I wasn't sure which would be the most logical default for this second argument, so I chose not factor in hidden rows or columns (that is, the functions return the maximum row and column for only the visible data); if desired, this can be easily changed in the declaration headers for each function. Rick "Steve" wrote in message ... No Rick I'm looking for a VB or macro solution so that I can change chart ranges on the fly. "Rick Rothstein (MVP - VB)" wrote: Do you mean by using a worksheet formula? If so, do you have a column that will always have data in the last row? If so, give this formula a try (where I have assumed that column with data in the last row is A)... =MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0) Rick "Steve" wrote in message ... How would I find the last (bottom row) of data when data may be missing in some columns? I'm trying to set Chart options on the run with macros instead of setting the bottom row to a default value such as row 1000. The number of rows is changing depending on options picked from a ListBox. Thanks Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find data based on conditon of a maximum date | Excel Discussion (Misc queries) | |||
?How can I find the maximum column with condition | Excel Discussion (Misc queries) | |||
Find Maximum date | Excel Worksheet Functions | |||
find maximum | Excel Discussion (Misc queries) | |||
find which column has the maximum value | Excel Discussion (Misc queries) |