Select all cells in a column containing a text not a formula

Question

I'd like to ask you this question: I have in column B of my Excel worksheet these cells

I want to select only B1, B2, B4; in other words I want to select ONLY CELLS OF COLUMN B WHICH CONTAIN A TEXT, NOT A FORMULA

Is it possible do that in a code as:

rng = 'I DON'T KNOW'
For Each cella In rng
..

score 2 · Answer 1 · answered Jul 16 '14 at 11:45

2

Excel has this function built into SpecialCells, so you can do this:

Set rng = Sheets(2).Range("B1:B4").SpecialCells(xlCellTypeConstants)

Looping through worksheet ranges in Excel is very slow and best avoided

answered Jul 16 '14 at 11:45

SWa

Noldor130884 · Answer 2 · 2014-07-16T10:16:06.323

1

you can use the .HasFormula command in a line written more or less as:

If Not cella.HasFormula Then

In this case you will need to copy/select (or whatever) the cell, otherwise not. Put this in your for loop and you're done!

edited Jul 16 '14 at 10:16

answered Jul 16 '14 at 10:08

Noldor130884

score 0 · Answer 3 · edited Mar 08 '18 at 12:16

0

Columns("B:B").Select

Selection.SpecialCells(xlCellTypeConstants, 23).Select

edited Mar 08 '18 at 12:16

Bugs

answered Jan 25 '18 at 23:12

vigilent

3 Answers3