Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 24th 20, 06:34 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 15
Default 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   Report Post  
Old October 25th 20, 05:26 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 72
Default 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   Report Post  
Old October 25th 20, 06:02 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 15
Default 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   Report Post  
Old October 25th 20, 07:11 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 72
Default 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   Report Post  
Old October 25th 20, 07:49 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 15
Default 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   Report Post  
Old October 25th 20, 08:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 72
Default 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   Report Post  
Old October 25th 20, 09:39 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 15
Default 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   Report Post  
Old October 25th 20, 11:25 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 72
Default 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   Report Post  
Old October 26th 20, 01:44 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2012
Posts: 15
Default 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   Report Post  
Old October 26th 20, 09:48 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2019
Posts: 72
Default 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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Proper syntax for selecting a range using cells instead of range JasonK[_3_] Excel Programming 4 July 22nd 09 07:01 AM
Selecting range in list of range names depending on a cell informa Courreges Excel Discussion (Misc queries) 2 June 19th 06 10:59 AM
Help please in selecting range dependent on another range MickJJ Excel Programming 2 January 10th 05 01:01 PM
Selecting a Range inside a range hcova Excel Programming 0 July 13th 04 03:26 PM
Selecting a range PCOR Excel Programming 4 December 27th 03 09:05 PM


All times are GMT +1. The time now is 09:35 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright ©2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017