Set first blanl cell as range

mattress58

Board Regular
Joined
Aug 7, 2013
Messages
55
I need to adjust the code below. I need to set the first blank cell in column E as a perminent spot for later code. If E29 is the first blank cell I want FB = Range("E29") and not the first blank cell. I only want to use Range("E500").End(xlUp) to just find and set the cell in the beginning and then it never changes as data fills in column E.


HTML:
Sub Try
Dim FB as range
set FB = Range("E500").End(xlUp)
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
I need to adjust the code below. I need to set the first blank cell in column E as a perminent spot for later code. If E29 is the first blank cell I want FB = Range("E29") and not the first blank cell. I only want to use Range("E500").End(xlUp) to just find and set the cell in the beginning and then it never changes as data fills in column E.

Rich (BB code):
Sub Try
Dim FB as range
set FB = Range("E500").End(xlUp).Offset(1)

Try adding the part in red.
 
Upvote 0
Code:
set FB = Range("E500").End(xlUp)[B][COLOR=#ff0000].Offset(1)[/COLOR][/B]

Gets me the first blank cell which is perfect, but how do I set FB to that cell from here on out. As data gets intered into column E, I want FB to equal E29 in the example.

Example
E29 is the first blank cell in column e

I do

Code:
Dim FB as range
set FB = Range("E500").End(xlUp)[B][COLOR=#ff0000].Offset(1)[/COLOR][/B]

then my macro fills in data into E36

I want FB to still equal E29

I just want to use
Code:
Range("E500").End(xlUp)[B][COLOR=#ff0000].Offset(1)[/COLOR][/B]
to set FB to that cell and then it always equals that cell even as the macro goes on and things get filled into column E.
 
Upvote 0
Code:
set FB = Range("E500").End(xlUp)[B][COLOR=#ff0000].Offset(1)[/COLOR][/B]

Gets me the first blank cell which is perfect, but how do I set FB to that cell from here on out. As data gets intered into column E, I want FB to equal E29.
Do you want that functionality only while the workbook is open and every resets the next time the workbook is opened? Or do you want to run the macro for the first time and then have that same cell memorized for the rest of time?
 
Upvote 0
I want it to set it each time the macro is ran. It sets it at the begnning and then keeps it unitl end sub.
 
Upvote 0
I want it to set it each time the macro is ran. It sets it at the begnning and then keeps it unitl end sub.
:confused: Sorry, but I am confused... if you only run the code snippet at the beginning of the sub, and don't execute it again anywhere else in the code, then FB would remain set to that cell until the macro ends.
 
Upvote 0
I will be referencing the spot in the rest of the code.

such as FB.offset(1,0) to get values as they are filled in.
 
Upvote 0
I will be referencing the spot in the rest of the code.

such as FB.offset(1,0) to get values as they are filled in.
That does not end my confusion. Let me repeat (I think this should be clear)... if you only run the code snippet, namely this...

Code:
set FB = Range("E500").End(xlUp)[COLOR=#000000].Offset(1)[/COLOR][COLOR=#B22222][/COLOR][COLOR=#B22222][/COLOR][COLOR=#B22222][/COLOR]

at the beginning of the sub, and don't execute it again anywhere else in the code, then FB would remain set to that cell until the macro ends. You can offset from it all you like as the cell FB is referencing won't change.
 
Upvote 0
AWWWWW..... Sorry, you are correct... I had an error on another part. I thought everytime I put FB it was like putting
Code:
Range("E500").End(xlUp)[COLOR=#000000].Offset(1)[/COLOR]
Which would look again at the first blank cell.
I tried it after you first put that but I have the cell reference wrong.
Thank you for the help. This really helps me with what I am doing.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top