Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-20-2017, 11:53 PM
mreynolds1775 mreynolds1775 is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default Macro to select cell based on formula

Hello all,

I record macro's the lazy way, but recording them. Typically this is fine as all actions are recorded by the mouse controls, but I would like to have the first step in the macro select the last cell containing a value in a row, then selecting a vertical range below that cell.



Any idea how to attack this?

Thanks in advance
Reply With Quote
  #2  
Old 09-21-2017, 12:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Moved to VBA programming forum
Reply With Quote
  #3  
Old 09-21-2017, 12:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,901
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

I think you can find this type of code easily on the web
Reply With Quote
  #4  
Old 09-22-2017, 09:04 AM
mreynolds1775 mreynolds1775 is offline Windows 10 Office 2013
Novice
 
Join Date: Sep 2017
Posts: 12
mreynolds1775 is on a distinguished road
Default

Peco, et. al,

Thanks for the help. I've been able to get the cell next to the last cell by using the following VBA:

Code:
Selection.End(xlToRight).Offset(0,1).Select
now that I have that cell selected, I can enter the desired formula. I however want to paste that formula through the remainder of the rows... problem is I don't want to type in a specific range, I want to use the range associated with the selected cell...

any ideas how to use an xlToBottom, but selecting all cells between the two ranges? In example B2:F2
Reply With Quote
  #5  
Old 09-22-2017, 12:13 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Posts: 388
NoSparks is on a distinguished road
Default

If you ignore this line in post #4
Quote:
any ideas how to use an xlToBottom, but selecting all cells between the two ranges? In example B2:F2
And assuming the active sheet is the one you want to work on and assuming row 2 is the row you want to find the end of,
I think this answers what you've asked
Code:
Sub Test()

Dim c As Long
    
c = Cells(2, Columns.Count).End(xlToLeft).Column
Range(Cells(2, c + 1), Cells(Rows.Count, c).End(xlUp).Offset(0, 1)).Select

End Sub
Seeing this is a single range in an unknown column, how does example B2:F2 relate ?
Reply With Quote
Reply
Please reply to this thread with any new information or opinions.

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto enter data based on formula in another cell? RonLEngle Excel 5 06-30-2017 11:44 AM
Change formula cell range based on cell value Scoth Excel 4 10-25-2012 07:51 AM
Creating formula based on if data is correct in cell MattMurdock Excel 1 08-06-2012 03:11 AM
Macro based on cell value ubns Excel Programming 1 05-07-2012 04:03 AM


All times are GMT -7. The time now is 03:03 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft