On 30 Mrz., 14:24, Co wrote:
Hi All,

I use a code in VBA that will extract text from an excel worksheet.
I use the UsedRange option to get the row and colums with text.
Recently I had a worksheet that had 14434 rows of which maybe 20 were
with text.
The code kept cycling until it reached row 14434.
Is there a way to get only the lines with text?

hi Marco,
maybe you could use SpecialCells.

col = 1
Set CellsUsed = Columns(col).SpecialCells(xlCellTypeConstants)
For Each r In CellsUsed
'For ColNdx = StartCol To EndCol
MsgBox Cells(r.Row, col)
'next
Next

if you have text in A1, A100 and A1000,
For each r cycles from 1 to 3.

some limitations:
a cell must contain text, not a formula (see VBA help for
SpecialCells)
if there`s a text in a row, the first column must have a text

stefan