Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Variable range
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Variable range
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Variable range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Variable range
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 "mathel" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy a Variable range
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA help to copy variable range | Excel Discussion (Misc queries) | |||
Copy Variable Range to New Worksheet | Excel Programming | |||
How to Copy and Paste a variable range | Excel Programming | |||
Macro to copy a specified range to a variable range | Excel Programming | |||
Locating variable range to copy | New Users to Excel |