Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a variable in a range
Thank you Bob, more clear for newbies like me.
Tempy *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to get a variable range | Excel Programming | |||
setting a range variable equal to the value of a string variable | Excel Programming | |||
Using Variable in RANGE | Excel Programming | |||
Using Variable in RANGE | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |