Hi. Mahesh,
OFFSET is either an Excel function or a VBA method. I’ll explain the Excel function first. It is simplest to give some examples : =OFFSET(B2,1,0) is equal to : B3 (shifting one row downwards) =OFFSET(B2,-1,0) is equal to : B1 (shifting one row upwards) =OFFSET(B2,0,2) is equal to : D2 (shifting two columns to the right) =OFFSET(B2,2,2) is equal to : D4 (shifting two rows downwards and two columns to the right) Furthermore, you can select a range : =OFFSET(B2,0,0,5,2) Means you select a range 5 rows x 2 columns, starting from B2. =OFFSET(B2,-1,0,5,2) Means you select a range 5 rows x 2 columns, starting from B1. =SUM(OFFSET(B2,-1,0,5,2)) is equivalent to : =SUM(B1:C5) The VBA method enables you to make shifts. You’ll have to use the Resize property to define multi cells ranges : The equivalent of =OFFSET(B2,-1,0,5,2) Would be : [B2].Offset(-1,0).Resize(5,2) Hope this helps. Daniel De : excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] De la part de Mahesh Envoyé : mercredi 8 juillet 2009 06:40 À : excel-macros@googlegroups.com Objet : [Norton AntiSpam] $$Excel-Macros$$ Use of Offset Hi All, Please tell me what is the use of OFFSET Command. -- With Love, Mahesh Bisht --~--~---------~--~----~------------~-------~--~----~ ---------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 5,000 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~----------~----~----~----~------~----~------~--~---
