Select all written cells starting from C4 until the last available cell in the same column C

[Klash Ville]

Hello everyone,

I have a very simple problem:

How do I select all written cells starting from C4 until the last available cell in that same column C?

 

[Fluff]

Helps if I put my brain in gear, before posting!

    Dim Rng As Range
    
    Set Rng = Range(ActiveCell.Address, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
    Union(Rng.SpecialCells(xlCellTypeConstants), Rng.SpecialCells(xlCellTypeFormulas)).Select

One word of warning. This will only work if you have both Constants & formulae in the column

 

[Klash Ville]

Ok, so I fixed the part that it wasn't counting blank cells and written cells within a specific range by modifying your original code into this:

Dim Rng As Range    
Set Rng = Range("L4", Range("L" & Rows.Count).End(xlUp))
Union(Rng.SpecialCells(xlCellTypeConstants), Rng.SpecialCells(xlCellTypeVisible)).Select
    
MsgBox Rng.Rows.Count

Although, I need to know how to convert that L4 and L into the ActiveCell... which is the only missing thing

 

[Klash Ville]

Helps if I put my brain in gear, before posting!

    Dim Rng As Range
    
    Set Rng = Range(ActiveCell.Address, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
    Union(Rng.SpecialCells(xlCellTypeConstants), Rng.SpecialCells(xlCellTypeFormulas)).Select

One word of warning. This will only work if you have both Constants & formulae in the column

And everything is working properly now. Thank you very much @Fluff =)

this is the final result that I have that will be applied into multiple shunks of code, this is perfect, thank you for your assistance

Dim Rng As Range
    
Set Rng = Range(ActiveCell.Address, Cells(Rows.Count, ActiveCell.Column).End(xlUp))
Union(Rng.SpecialCells(xlCellTypeConstants), Rng.SpecialCells(xlCellTypeVisible)).Select
    
MsgBox Rng.Rows.Count

 

[Fluff]

The code you have posted above is going to select all the blanks cells (unless they've been hidden). Is that what you want?
In post#5 you said that Weeble' code did not do what you want, but it will give the same result as your code above!

 

[Klash Ville]

The code you have posted above is going to select all the blanks cells (unless they've been hidden). Is that what you want?
In post#5 you said that Weeble' code did not do what you want, but it will give the same result as your code above!

Do not worry, I have tested my version of your code mixed with your ActiveCell solution, and it works flawlessly. It selects all written and blank cells until the last written cell in that same target column only. It's perfect and exacly what I needed =)

Yes, Weeble's code has the same result had the original that I have initially used. I posted it anyways to give a base idea of what I tried, but anyways =)

 

[Fluff]

As long as you're happy that's fine

 

[Klash Ville]

As long as you're happy that's fine

I just noticed that the current code works 100% with cells with text.

Although when it comes to cells with numbers, I replaced:

[COLOR=#333333][FONT=&quot]xlCellTypeConstants[/FONT][/COLOR]

to:

[COLOR=#333333][FONT=&quot]xlCellTypeFormulas[/FONT][/COLOR]

Since all those numbers come from formulas, it works flawlessy. Although, do you know a way to direcly detect numbers?

 

[Klash Ville]

BUMP to my last reply:

[COLOR=#333333]xlCellTypeFormulas
[/COLOR][/COLOR]

Since all those numbers come from formulas, it works flawlessy. Although, do you know a way to direcly detect numbers?