I have a table with 56 columns and use Range("A2").CurrentRegion property to
copy it to another worksheet (archive). If each column contains at least one
value everything is OK. But sometimes one or more columns do not contain any
values (cells are empty). In this case CurrentRegion stops on first empty
column. Is it possible to use all the 56 table columns despite any possible
gaps in it?

Thank you in advance for your help.

Mike510

If you can use an explicit range eg. "A2:CA100" I would do so, otherwise
this will copy to the REAL last cell :-

'-----------------------------------------------------
Sub LastCell()
Set foundcell = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
'------------------------
ActiveSheet.Range(Cells(2, 1), Cells(foundcell.Row,
foundcell.Column)).Copy
End Sub
'------------------------------------------------------


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Range("A2").CurrentRegion.Resize(,56).copy

--
Regards,
Tom Ogilvy


Mikhail wrote in message
...
I have a table with 56 columns and use Range("A2").CurrentRegion property
to
copy it to another worksheet (archive). If each column contains at least
one
value everything is OK. But sometimes one or more columns do not contain
any
values (cells are empty). In this case CurrentRegion stops on first empty
column. Is it possible to use all the 56 table columns despite any
possible
gaps in it?

Thank you in advance for your help.

Mike510

Assuming there is always data in col A this code should do what you
want

Range(Range("A2"), Range("A2").End(xlDown).Offset(0, 56)).Select


DavidP

On Tue, 11 Nov 2003 16:12:12 +0300, "Mikhail"
wrote:

I have a table with 56 columns and use Range("A2").CurrentRegion property to
copy it to another worksheet (archive). If each column contains at least one
value everything is OK. But sometimes one or more columns do not contain any
values (cells are empty). In this case CurrentRegion stops on first empty
column. Is it possible to use all the 56 table columns despite any possible
gaps in it?

Thank you in advance for your help.

Mike510

Dear Excel experts!
Thank you all for your valuable help.
IMHO the most elegant solution is Range("A2").CurrentRegion.Resize(,56).copy
Thank you again.
Mike510