Monika,

Try something like

Range("B2",Cells(2, Range"Location").Row))

and stop selecting<Vbg! These 3 lines

Range("BE2").Select
Selection.NumberFormat = "#,##0"
Selection.Copy

can be written as

With Range("BE2")
.NumberFormat = "#,##0"
.Copy
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"monika" wrote in message
...
hi...

i normally get to face problems where i need to extract the column
alphabets...

like an eg of my code:
Range("BE2").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-20]<0,RC[-1]/(RC[-20]/100),RC[-1]/0.94)"
Range("BE2").Select
Selection.NumberFormat = "#,##0"
Selection.Copy

here is another example:

Range("Y2").Select
ActiveCell.FormulaR1C1 = _


"=IF(OR(RC[-1]=""SI"",RC[-1]=""BI""),""Inhouse"",IF(RC[-1]=""MS"",""MAL"",IF
(OR(RC[-1]=""WR"",RC[-1]=""WQ""),""IFWS"",""Subcon"")))"
Range("Y2").Select
Selection.Copy
Range("Y3:Y" & Format(lastCellNum)).Select
ActiveSheet.Paste
Application.CutCopyMode = False


I dont wan t to refer to these columns as BE...or Y2... its hard coding.
This particular column Y2 has the heading of LOCATION. So i can find out
the
column through the address...address returns me $Y$2....but how do i
extract the Y from it???

thanks in advance
monika