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

This is really confusing an old man. You really need to explain, with
examples.
>>2- Select K?? to H1 in order to copy the selected range.



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"mathel" <(E-Mail Removed)> wrote in message
news:B3721BDC-F8C3-403F-99F9-(E-Mail Removed)...
> 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

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

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

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.
--
Linda


"Don Guillett" wrote:

> This is really confusing an old man. You really need to explain, with
> examples.
> >>2- Select K?? to H1 in order to copy the selected range.
>
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "mathel" <(E-Mail Removed)> wrote in message
> news:B3721BDC-F8C3-403F-99F9-(E-Mail Removed)...
> > 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
>
>

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
(E-Mail Removed)
"mathel" <(E-Mail Removed)> wrote in message
news:FDD25BD4-A2AC-4233-B57C-(E-Mail Removed)...
> 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