Sub copytextrowsonly()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("a2:a" & lr).SpecialCells(xlConstants, xlTextValues) _
.EntireRow.Copy Range("a17")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"mathel" wrote in message
...
You are correct in what you are saying, the Range shown to copy is H1 to
L46,
however, I need to find the last row containing 'text', not the entire
original range. What I need to do is copy only the rows with text data to
another workbook.

I hope this helps to explain what I am trying to do.

Thanks for any help.
--
Linda


"JLGWhiz" wrote:

lastRow = Cells(Rows.Count, "K").End(xlUp).Row

This finds the last row with data in column K at the time the line is
executed.
From what you have described that would be row 45.

Range("H1:L" & lastRow).Copy

This captures a block containing columns H, I, J, K and L from row 1
down
to row 45, so it would include H1 thru K45. If you want only the range
you
entered the formula in then it would be: Range("L" & lastRow +1).Copy
Which by your example would be L46.

If this doesn't answer the question, maybe you could give a better
explanation of the problem.

"mathel" wrote:

Hi guys,

I know this question has been answered a number of times, however, no
matter
what I try, I can't get it.

I have a worksheet where Range A1 to D46 has formulas to pull data from
another sheet. I am taking this range and doing Paste Special/Values
to Cell
H1 then doing a sort so there are no blank rows.

I need to do 2 things..
1- find the last row to put a formula in Column L (ie: Sum(K1:K??-
whatever
the last cell is)
and
2- Select K?? to H1 in order to copy the selected range.

I have tried the following to test if I could select the range, but it
selects the entire range from H1 to K45, I have no idea how to get
around
this.

Dim lastRow As Long
lastRow = Cells(Rows.Count, "K").End(xlUp).Row
'set variable to the last used row in K
Range("H1:L" & lastRow).Copy


Help with this would be appreciated

--
Linda