Selecting cells in the column

[topi1]

Hi, I am sharing a link to the table since Xl2bb gives me 1044-application-object... error.
In the column B of the sheet there are formula till row 14. Some cells are blank ("") and some show numbers. the last cell with the value is B10 although there are formulas in the B11-B14 cells.
I use the following formula to find the last nonblank cell in the column B even if there are blank cells before that.
=INDEX(B2:B140,AGGREGATE(14,6,(ROW(B2:B140)-ROW(B2)+1)/(B2:B140<>""),1))
Is it possible to use something like this and find a way to select a range from B2 to this last filled cell? Blank cells with formulas should not be included.
I tried to copy and paste values in another column and then try to select the range by selecting C2 and then Ctrl+End. But that selects C2-C14 range and not C2-C10
File is saved as .xlsm but I have no vba for this.

Any help will be appreciated. Thank you in advance.

9.7 KB file on MEGA

mega.nz

 

[topi1]

No that is not a valid range in any code

You would need something like

Range("B1:B" & Range("B" & Rows.count).End(xlUp).Row)

Ok. Thank you. I need to learn about range value. I can see that sometimes I could make use of similar thing as a formula than vba. I tried some before leaving for work and was getting #value error. TY.

 

[MARK858]

You need to specify a number for both the start row and the end row unless dealing with the entire column in VBA

 

[topi1]

You need to specify a number for both the start row and the end row unless dealing with the entire column in VBA

Ok. Thank you.

 

[topi1]

No that is not a valid range in any code

You would need something like

Range("B1:B" & Range("B" & Rows.count).End(xlUp).Row)

I like this even better! Thank you very much!

 

[MARK858]

You're welcome, happy we could help