how to fill gaps in Current Region?

  • Thread starter Thread starter Mikhail
  • Start date Start date
M

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