variable range in a macro

[Guest]

I have a worksheet where the number of rows and columns vary each time I
process the data.
I want to transpose the cols and rows but need to set the range before I
copy. There may not be any data in the XY cell. Is there a way to do this in
a macro?
Thanks for any help.

 

[Norman Jones]

Hi Al,

Assume data starts in A1 and that there is a blank row below, and a blank
column to the right, of the data, then your data can be defined by:

ActiveSheet.Range("A1").CurrentRegion

 

[\VoG via OfficeKB.com\]

LastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

LastCol = Cells(1, 256).End(xlToLeft)

 

[Bob Phillips]

or being consistent

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column