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

 

[Peter_SSs]

Xl2bb gives me 1044-application-object... error.

I would suggest uninstalling & re-installing XL2BB - see step details under the heading Installation a short way down this page.

Why do you want to actually select those cells? What are you going to do next? It may be possible to do get the result of the next step without actually selecting that range first.
Tell us a bit more about your overall goal.

 

[topi1]

I would suggest uninstalling & re-installing XL2BB - see step details under the heading Installation a short way down this page.

Why do you want to actually select those cells? What are you going to do next? It may be possible to do get the result of the next step without actually selecting that range first.
Tell us a bit more about your overall goal.

It could be for variety of use. The most common use for me will be that I have hundreds of formulas and they fill with information as time passes. Formulas get replaced with values. So the last "active" row is never set. So if I want to include entire data in the column it could be A2 to A39 and then the next time it will be A2 to A40. Thank you.

The other scenario is that there are say 100 rows of formula in the columns B and columns C. And I paste data in the column A (variable row each time). I then want to capture relevant cells from A to C columns and paste as values elsewhere. But it becomes a problem when it captures blanks too. Thank you.

 

[topi1]

I would suggest uninstalling & re-installing XL2BB - see step details under the heading Installation a short way down this page.

Why do you want to actually select those cells? What are you going to do next? It may be possible to do get the result of the next step without actually selecting that range first.
Tell us a bit more about your overall goal.

If B5=if((A5<>""),A5,"") and A5 is empty; and I copy B5 and paste it as value in C5, why is C5 not being recognized as completely blank. Should I be altering these blanks in someways? This is where I tend to stumble.

 

[topi1]

The following discussion seems to be relevant to my question, but I don't understand the conversation.

Copy/Paste Range issue with VBA when cells are empty (but with formulas inside)

Hello, Currently having some issues when copy/pasting some range of rows. There are some formulas in there that would be populating depending of another table. So in the end there would be some rows populating from such table, and eventually remaining rows without populated values would remain...

www.mrexcel.com

 

[MARK858]

@topi1 use

Sub topi()
   Range("C5").Value = Range("B5").Value
End Sub

rather than PasteSpecial xlValues

 

[topi1]

@MARK858 Fantastic. This is exactly I was looking for. To not recognize blank cells with formulas. I modified your code in case I was to select and copy C range including in-between blank rows. Thank you much for your help.

Sub topi()
   Range("c1:c14").Value = Range("B1:b14").Value
   Range(Range("c1"), Cells(Rows.Count, 3).End(xlUp)).Copy
End Sub

 

[MARK858]

You're welcome

Just in case you ever don't know how many cell the original range has and don't want to count them use...

Sub topi2()
    With Range("B1:b14")
        Range("C1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub

 

[topi1]

Could I use
Range("B1:B")?
Thank you for your help.

 

[MARK858]

Could I use
Range("B1:B")?

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)