Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
I'd like to name a range and go to the last row with data in that range.
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
"David Godinger" wrote in message
I'd like to name a range and go to the last row with data in that range. one way - Dim rng As Range, rngBR As Range Set rng = ActiveSheet.Range("B2:Z100") With rng Set rngBR = .Cells(.Rows.Count, .Columns.Count) End With 'Debug.Print rngBR.Address Application.Goto rngBR Peter T |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
The macro took me to the last cell (the lower right of the entire range). By the way, the lower half of the range had no text or numbers in it.
However, there was formatting in all cells of the range for color, number, and border. Could it be that the macro considers the formatting as data? If so, would it be possible for the macro to work only on numbers, dates, and text? Thanks! Dave G. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
"David Godinger" wrote in message
The macro took me to the last cell (the lower right of the entire range). By the way, the lower half of the range had no text or numbers in it. However, there was formatting in all cells of the range for color, number, and border. Could it be that the macro considers the formatting as data? If so, would it be possible for the macro to work only on numbers, dates, and text? Sorry I misread you wanted the last row 'with data'. It would simplify things if you wanting the last cell in a specified column (rather than all columns that includ data, for example the in the first column of the given range - If that's that an option try this last data cell in first column of the range lastRow = rng.Offset(rng.Rows.Count, 1 - 1).Cells(1).End(xlUp).Row ' lastRow = actual row ' or last row with data in col-1 of the range lastRow = lastRow - rng.Rows(1).Row + 1 Debug.Print rng(lastRow, 1).Address Peter T |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
Yes, getting the last cell of text or numbers in one column is easy, but that's not what I'm asking for.
Again, I've appreciated your thoughts, thanks. On Sunday, October 25, 2020 at 11:11:56 AM UTC-7, Peter T wrote: "David Godinger" wrote in message The macro took me to the last cell (the lower right of the entire range). By the way, the lower half of the range had no text or numbers in it. However, there was formatting in all cells of the range for color, number, and border. Could it be that the macro considers the formatting as data? If so, would it be possible for the macro to work only on numbers, dates, and text? Sorry I misread you wanted the last row 'with data'. It would simplify things if you wanting the last cell in a specified column (rather than all columns that includ data, for example the in the first column of the given range - If that's that an option try this last data cell in first column of the range lastRow = rng.Offset(rng.Rows.Count, 1 - 1).Cells(1).End(xlUp).Row ' lastRow = actual row ' or last row with data in col-1 of the range lastRow = lastRow - rng.Rows(1).Row + 1 Debug.Print rng(lastRow, 1).Address Peter T |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
"David Godinger" wrote in message
Yes, getting the last cell of text or numbers in one column is easy, but that's not what I'm asking for. OK try this - Sub test() Dim rngData As Range Dim rngFound As Range Set rngData = Range("B2:J20") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) If rngFound Is Nothing Then MsgBox rngData.address & " is empty" Else MsgBox rngFound.Address End If End Sub Peter T |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
On Sunday, October 25, 2020 at 12:05:51 PM UTC-7, Peter T wrote:
"David Godinger" wrote in message Yes, getting the last cell of text or numbers in one column is easy, but that's not what I'm asking for. OK try this - Sub test() Dim rngData As Range Dim rngFound As Range Set rngData = Range("B2:J20") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) If rngFound Is Nothing Then MsgBox rngData.address & " is empty" Else MsgBox rngFound.Address End If End Sub Peter T OK, great! The message box found the correct cell. Could you please add a line to select that cell? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
"David Godinger" wrote in message
On Sunday, October 25, 2020 at 12:05:51 PM UTC-7, Peter T wrote: "David Godinger" wrote in message Yes, getting the last cell of text or numbers in one column is easy, but that's not what I'm asking for. OK try this - Sub test() Dim rngData As Range Dim rngFound As Range Set rngData = Range("B2:J20") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) If rngFound Is Nothing Then MsgBox rngData.address & " is empty" Else MsgBox rngFound.Address End If End Sub Peter T OK, great! The message box found the correct cell. Could you please add a line to select that cell? rngFound.Select If not on the activesheet start with - rngFound.Parent.Parent.activate rngFound.Parent.activate Or use Application.Goto as I suggested earlier, better if you want to bring the selected cell into the 'Visible.Range'. Peter T |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
On Sunday, October 25, 2020 at 3:25:24 PM UTC-7, Peter T wrote:
"David Godinger" wrote in message On Sunday, October 25, 2020 at 12:05:51 PM UTC-7, Peter T wrote: "David Godinger" wrote in message Yes, getting the last cell of text or numbers in one column is easy, but that's not what I'm asking for. OK try this - Sub test() Dim rngData As Range Dim rngFound As Range Set rngData = Range("B2:J20") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) If rngFound Is Nothing Then MsgBox rngData.address & " is empty" Else MsgBox rngFound.Address End If End Sub Peter T OK, great! The message box found the correct cell. Could you please add a line to select that cell? rngFound.Select If not on the activesheet start with - rngFound.Parent.Parent.activate rngFound.Parent.activate Or use Application.Goto as I suggested earlier, better if you want to bring the selected cell into the 'Visible.Range'. Peter T Hi Again Mr. T., The following works almost perfectly for me, except for an exception that may never even come up. The macro doesn't detect the contents of hidden columns. Thanks so much for helping me! I really appreciate it! Mr. G. Sub gotoEndOfRangeOfAllDataLeft() Dim rngData As Range Dim rngFound As Range Set rngData = Range("rangeOfAllDataLeft") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) rngFound.Select ActiveCell.Offset(1, 0).Select 'Move cursor down one row Cells(ActiveCell.Row, 29).Select 'Move cursor to column AC on same row End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
"David Godinger" wrote in message
On Sunday, October 25, 2020 at 3:25:24 PM UTC-7, Peter T wrote: "David Godinger" wrote in message On Sunday, October 25, 2020 at 12:05:51 PM UTC-7, Peter T wrote: "David Godinger" wrote in message Yes, getting the last cell of text or numbers in one column is easy, but that's not what I'm asking for. OK try this - Sub test() Dim rngData As Range Dim rngFound As Range Set rngData = Range("B2:J20") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) If rngFound Is Nothing Then MsgBox rngData.address & " is empty" Else MsgBox rngFound.Address End If End Sub Peter T OK, great! The message box found the correct cell. Could you please add a line to select that cell? rngFound.Select If not on the activesheet start with - rngFound.Parent.Parent.activate rngFound.Parent.activate Or use Application.Goto as I suggested earlier, better if you want to bring the selected cell into the 'Visible.Range'. Peter T Hi Again Mr. T., The following works almost perfectly for me, except for an exception that may never even come up. The macro doesn't detect the contents of hidden columns. Thanks so much for helping me! I really appreciate it! Mr. G. Sub gotoEndOfRangeOfAllDataLeft() Dim rngData As Range Dim rngFound As Range Set rngData = Range("rangeOfAllDataLeft") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) rngFound.Select ActiveCell.Offset(1, 0).Select 'Move cursor down one row Cells(ActiveCell.Row, 29).Select 'Move cursor to column AC on same row End Sub No need to Select three times - Cells(rngFound.Row + 1, 29).Select Indeed Find does't work with look in hidden columns, various other ways depending on your overall scenario. PT |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for Selecting Last Row in Range
On Monday, October 26, 2020 at 1:48:09 AM UTC-7, Peter T wrote:
"David Godinger" wrote in message On Sunday, October 25, 2020 at 3:25:24 PM UTC-7, Peter T wrote: "David Godinger" wrote in message On Sunday, October 25, 2020 at 12:05:51 PM UTC-7, Peter T wrote: "David Godinger" wrote in message Yes, getting the last cell of text or numbers in one column is easy, but that's not what I'm asking for. OK try this - Sub test() Dim rngData As Range Dim rngFound As Range Set rngData = Range("B2:J20") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) If rngFound Is Nothing Then MsgBox rngData.address & " is empty" Else MsgBox rngFound.Address End If End Sub Peter T OK, great! The message box found the correct cell. Could you please add a line to select that cell? rngFound.Select If not on the activesheet start with - rngFound.Parent.Parent.activate rngFound.Parent.activate Or use Application.Goto as I suggested earlier, better if you want to bring the selected cell into the 'Visible.Range'. Peter T Hi Again Mr. T., The following works almost perfectly for me, except for an exception that may never even come up. The macro doesn't detect the contents of hidden columns. Thanks so much for helping me! I really appreciate it! Mr. G. Sub gotoEndOfRangeOfAllDataLeft() Dim rngData As Range Dim rngFound As Range Set rngData = Range("rangeOfAllDataLeft") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) rngFound.Select ActiveCell.Offset(1, 0).Select 'Move cursor down one row Cells(ActiveCell.Row, 29).Select 'Move cursor to column AC on same row End Sub No need to Select three times - Cells(rngFound.Row + 1, 29).Select Indeed Find does't work with look in hidden columns, various other ways depending on your overall scenario. PT Thanks again! The following now does the job very well for me! Sub gotoEndOfRangeOfAllDataOnLeft1() ' *** Thanks to Peter T ' *** Who on Sunday, Oct. 25, 2020, patiently created this macro for me ' *** On the newsgroup https://groups.google.com/g/microsof...el.programming 'This macro moves the cursor to the bottom of all columns in the range with text or numbers 'And then moves the cursor down one row and to a specific column Dim rngData As Range Dim rngFound As Range Set rngData = Range("rangeOfAllDataLeft") Set rngFound = rngData.Find("*", rngData.Cells(1), xlValues, , xlByRows, xlPrevious) Cells(rngFound.Row + 1, 29).Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Proper syntax for selecting a range using cells instead of range | Excel Programming | |||
Selecting range in list of range names depending on a cell informa | Excel Discussion (Misc queries) | |||
Help please in selecting range dependent on another range | Excel Programming | |||
Selecting a Range inside a range | Excel Programming | |||
Selecting a range | Excel Programming |