Using a variable in a range
 

Hi all,

I have the following code which determines the amount of rows:
Dim lastrow As Long
lastrow = range("A6").End(xlDown).Row

I now need to use the "lastrow" in the formula from Bob

For Each cell In ActiveSheet.range("BD6:BD10000")
If Not cell.HasFormula Then
cell.Value = Trim(cell.Value)
End If
In other words all the spreadsheets start at BD6 but the lenght is
variable. I have tried to piece it together but cannot get it to work.

thanks

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Using a variable in a range
 

Range(Cells(6, 56), Cells(lastrow, 56))

--

Vasant

"Tempy" wrote in message
...
Hi all,

I have the following code which determines the amount of rows:
Dim lastrow As Long
lastrow = range("A6").End(xlDown).Row

I now need to use the "lastrow" in the formula from Bob

For Each cell In ActiveSheet.range("BD6:BD10000")
If Not cell.HasFormula Then
cell.Value = Trim(cell.Value)
End If
In other words all the spreadsheets start at BD6 but the lenght is
variable. I have tried to piece it together but cannot get it to work.

thanks

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Using a variable in a range
 

Thanks for the reply Vasant, not being a programmer i do not know how
that goes together, could you help ?

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Using a variable in a range
 

I would use

Range("BD6:BD" & lastrow)

I think it is more explanatory :-)

I also suggest you work bottom up to get lastrow, unless you deliberately
want to avoid any rows after the first blank

lastrow = Cells(Rows.Count,"A").End(xlUp).Row

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
Range(Cells(6, 56), Cells(lastrow, 56))

--

Vasant

"Tempy" wrote in message
...
Hi all,

I have the following code which determines the amount of rows:
Dim lastrow As Long
lastrow = range("A6").End(xlDown).Row

I now need to use the "lastrow" in the formula from Bob

For Each cell In ActiveSheet.range("BD6:BD10000")
If Not cell.HasFormula Then
cell.Value = Trim(cell.Value)
End If
In other words all the spreadsheets start at BD6 but the lenght is
variable. I have tried to piece it together but cannot get it to work.

thanks

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Using a variable in a range
 

Thank you Bob, more clear for newbies like me.

Tempy

*** Sent via Developersdex http://www.developersdex.com ***

Using a variable in a range
 

"Bob Phillips" wrote in message
...
I think it is more explanatory :-)


Ah, but then one loses one's mystique <g.

Regards,

Vasant