Select Everything until empty row

[Lorlai]

I have some VBA code that I am trying to use. I want to select all data from row A1 until the last used cell before an empty row. For instance, I want to be able to select the Range A1:Z100 without knowing that the last piece of data is in Z100. How can I do this in VBA?

Additionally, how can I take this new found range and store it so I can put it into this line of code:

wsnew.Range([COLOR=Red]Need to Put Range Here[/COLOR]).Offset(, wsnew.Cells(1, Columns.Count).End(xlToLeft).Column + 2).Formula = "=IF(A1=A203,A1,""!"" & A1)"

Thank you for your help!

 

[Smitty]

Here's one way:

Dim LR as Long
LR = Cells(Rows.count,"A").End(xlup).Row
Range("A1:Z" & LR)...

HTH,