Macro to select cell based on formula

mreynolds1775 · #1 09-20-2017, 11:53 PM

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

Pecoflyer · #2 09-21-2017, 12:29 AM

Moved to VBA programming forum

Pecoflyer · #3 09-21-2017, 12:29 AM

I think you can find this type of code easily on the web

mreynolds1775 · #4 09-22-2017, 09:04 AM

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

NoSparks · #5 09-22-2017, 12:13 PM

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 ?

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