Copying RANGE of variable length

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Back
Top