Help with End Macro

[ckserra]

Hi everyone. I deal with a sheet that adds a column each day. I am trying to write a macro that copies and pastes the last column on a different tab. I have struggled with this for a few hours. The macro I have simply selects the row, not the final column. Any help would be fantastic!

Here is the code I am using

Range("A1", Range("A1").End(xlToRight)).Select

Thanks

Chris

 

[Rick Rothstein]

Hi everyone. I deal with a sheet that adds a column each day. I am trying to write a macro that copies and pastes the last column on a different tab. I have struggled with this for a few hours. The macro I have simply selects the row, not the final column. Any help would be fantastic!

Range("A1", Range("A1").End(xlToRight)).Select

If you want to select the last column's data, this code line assumes no blank rows or blank columns within the data...

[table="width: 500"]
[tr]
	[td]Intersect(Cells(1, Columns.Count).End(xlToLeft).EntireColumn, Range("A1").CurrentRegion).Select[/td]
[/tr]
[/table]

 

[ckserra]

If you want to select the last column's data, this code line assumes no blank rows or blank columns within the data...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Intersect(Cells(1, Columns.Count).End(xlToLeft).EntireColumn, Range("A1").CurrentRegion).Select[/TD]
[/TR]
</tbody>[/TABLE]

Hi Rick. That works great on my example spreadsheet, but wont work on my real sheet since there are blank rows I cannot change or eliminate. Also, is there a way to change which row it sees the last column of data?

Thanks for your help!

Chris

 

[Rick Rothstein]

Hi Rick. That works great on my example spreadsheet, but wont work on my real sheet since there are blank rows I cannot change or eliminate. Also, is there a way to change which row it sees the last column of data?

This should work no matter column or row defines the end of the data and no matter how many blank rows or columns there are...

[table="width: 500"]
[tr]
	[td]With Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
  Range(Cells(1, .Column), Cells(Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row, .Column)).Select
End With[/td]
[/tr]
[/table]

 

[ckserra]

This should work no matter column or row defines the end of the data and no matter how many blank rows or columns there are...

[TABLE="width: 500"]
<tbody>[TR]
[TD]With Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
  Range(Cells(1, .Column), Cells(Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row, .Column)).Select
End With[/TD]
[/TR]
</tbody>[/TABLE]

PERFECT!! Thank you very much!

Chris