How to loop adding a column with row numbers beside each column in a dynamic range in Excel 2013

Occasional Contributor
  • Good day. Been on this for more than a week and can't find a solution. New to VBA. I have a data sheet with multiple columns of data. I would like to add, beside each column, a column with the row numbers of each column, and to do this through the range till it hits an empty column. Something like below. My data looks like the first pic. I'd like a VBA that loops through that and turns it into the second pic. I currently use Excel 2013. Would be grateful for any response

EDIT: had issues adding images so i uploaded them to a hosting site. The first image, what my worksheet currently looks like:


What i would like it to look like:


UPDATE: The excel worksheet with sample data Sample Data Sheet 



9 Replies

@Kenechukwu_Oraelosi hello, I do not see either of the pics mentioned attached.  Please kindly provide so that the community can visualize your intent.  


Thank you,



you should be able paste (or drag & drop as shown below) directly into these message boxes.   




Hi Dexter, i finally found a way to add images, as links. Please see the edited post. Would really be grateful for your help



The quick and dirty way to accomplish this with one formula (Requires Excel 365) is:



I know there's a way to clean this up and account for the different range sizes but wanted to get something to you which was directionally correct to get you started.   



Thank you for the quick response! I don't have Excel 365, i used the desktop version of Excel 2013 (old i know, its a friend's laptop while mine's undergoing repairs). Also its close. What i ultimately want to do with it is (1) in a column create a distinct list of values, then (2) beside each unique value place the sum of the rank numbers of all the times those values appear in the range. Also (3) i'll be adding columns to the sheet so the range would be dynamic, so it would need to loop through each column in the range and automatically stop at the end of the range

@Kenechukwu_Oraelosi Thanks for the clarification.  I'll unfortunately have to defer to one of the other community experts knowledgeable with legacy arrays.   Excel 365 makes these array formulas so much easer that even I can grasp them.      

Thank you so much already for your response, for taking your time to look at this. Really grateful honestly.