Copy a Variable range

[mathel]

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

 

[Don Guillett]

This is really confusing an old man. You really need to explain, with
examples.

 

[JLGWhiz]

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]

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.

 

[mathel]

Sorry for the delay in responding..blindsided at work with another project,
and my apologies for not being clearer.

In a wb there are 3 ws. Sheet 1 is used to input all data (names, account
numbers, $$, etc. The dollar amount on each row (or specific to an account)
can be in 1 of 2 columns - 'Active Accts' or 'Closed Accts'. On ws 3 I have
formulas from A1 to D46 to have a list of only those accounts that have a $$
figure under 'Closed Accts' - there could be 5 - 20 entries on different
rows, with blanks in between.

I need to copy the account information to another WB, but I don't want to
copy any of the blank rows, nor do I want to lose the formulas on the WS.
So, I thought by copying range A1-D46 to H1, pasting as values, then doing a
sort, all the information is together. But, when I try to find the last
empty row (ie: go to the bottom of the WS and do end-up, it stops at cell
K46 (which is the bottom of the copy range. I need the last row that
contains 'text'. The vba I have in my 1st post finds Range H1 to K46.

I hope this explanation helps and there is a way to find the last row
containing the text.

 

[Don Guillett]

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