Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying RANGE of variable length
I am hoping somebody can help me write code that will allow me to copy a
range of variable length. I have a spreadsheet that will possibly have data from column A to N. While cells in Column A will ALWAYS have data up to the last row in Column A, the other cells in other columns could be blank. I need to copy the data from A1 to the end of column A and across to the last cell in column N. Here is my lame attempt at a code and it didn't work. Any help will be appreciated. Pele Worksheets(q).Range(("A1")).End (xlDown), Range(("A1")).End(xlDown).End(xlRight).Copy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying RANGE of variable length
The trick here is that to define the range you need two points, being the
diagonal corners of the range. The range A1:N100 is the exact same range as A100:N1... So the range could be defined as with sheets("Sheet1") .range(.range("A1").end(xlRight), .range("A1").end(xldown)) end with -- HTH... Jim Thomlinson "Pele" wrote: I am hoping somebody can help me write code that will allow me to copy a range of variable length. I have a spreadsheet that will possibly have data from column A to N. While cells in Column A will ALWAYS have data up to the last row in Column A, the other cells in other columns could be blank. I need to copy the data from A1 to the end of column A and across to the last cell in column N. Here is my lame attempt at a code and it didn't work. Any help will be appreciated. Pele Worksheets(q).Range(("A1")).End (xlDown), Range(("A1")).End(xlDown).End(xlRight).Copy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying RANGE of variable length
Jim,
Can you rewrite your code using Worksheets (q) instead of Sheets("sheet1") where q is a variable and also include a copy capability. I tried to change your code as shown below but it didn't work. For q = 4 To (Worksheets.Count - 1) Step 1 Worksheets(q).Select With Worksheets(q).Range(.Range("A1").End(xlRight), ..Range("A1").End(xlDown)).copy End With "Jim Thomlinson" wrote: The trick here is that to define the range you need two points, being the diagonal corners of the range. The range A1:N100 is the exact same range as A100:N1... So the range could be defined as with sheets("Sheet1") .range(.range("A1").end(xlRight), .range("A1").end(xldown)) end with -- HTH... Jim Thomlinson "Pele" wrote: I am hoping somebody can help me write code that will allow me to copy a range of variable length. I have a spreadsheet that will possibly have data from column A to N. While cells in Column A will ALWAYS have data up to the last row in Column A, the other cells in other columns could be blank. I need to copy the data from A1 to the end of column A and across to the last cell in column N. Here is my lame attempt at a code and it didn't work. Any help will be appreciated. Pele Worksheets(q).Range(("A1")).End (xlDown), Range(("A1")).End(xlDown).End(xlRight).Copy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable series length/range | Charts and Charting in Excel | |||
Using a Macro to Sum a Variable-Length Range (a Column) | Excel Programming | |||
Using a Macro to Sum a Variable-Length Range (a Column) | Excel Programming | |||
Using a Macro to Sum a Variable-Length Range (a Column) | Excel Programming | |||
Using a Macro to Sum a Variable-Length Range (a Column) | Excel Programming |