how to fill gaps in Current Region?

[Mikhail]

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

 

[BrianB]

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
'------------------------------------------------------

 

[Tom Ogilvy]

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

 

[DavidP]

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

 

[Mikhail]

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