Adding Column - VBA

[Vishaal]

Hi

Thanks in advance,

i have the following sheet

Excel 2010 32 bit

	A​	B​	C​	D​	E​	F​	G​
1​
2​		3025​	2004​	199.00​	2479​
3​		3020​	2003​	199.00​	2478​
4​		3023​	1999​	199.00​	2477​
5​		3024​	1995​	70.00​	2480​
6​		475​	1994​	105.00​	2607​
7​		3021​	1993​	220.00​	2606​
8​		1662​	1977​	90.00​	2609​
9​		3019​	1970​	150.00​	1363​
10​		3018​	1961​	185.00​	2557​
11​		3017​	1936​	145.00​	2469​
12​		3016​	1903​	250.00​	1362​
13​		3015​	1866​	155.00​	2544​
14​		3011​	1859​	155.00​	1373​
15​		3004​	1853​	185.00​	2545​
16​		3001​	1823​	195.00​	2316​
17​		2009​	1790​	240.00​	2468​
18​		2008​	1828​	235.00​	542​
19​		2007​	1776​	210.00​	549​
20​		2006​	1753​	175.00​	2573​
21​					​
22​					​

Sheet: Sheet1

and i want to add the column with S.NO.

Excel 2010 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		​
2​		1​	3025​	1​	2004​	1​	199.00​	1​	2479​
3​		2​	3020​	2​	2003​	2​	199.00​	2​	2478​
4​		3​	3023​	3​	1999​	3​	199.00​	3​	2477​
5​		4​	3024​	4​	1995​	4​	70.00​	4​	2480​
6​		5​	475​	5​	1994​	5​	105.00​	5​	2607​
7​		6​	3021​	6​	1993​	6​	220.00​	6​	2606​
8​		7​	1662​	7​	1977​	7​	90.00​	7​	2609​
9​		8​	3019​	8​	1970​	8​	150.00​	8​	1363​
10​		9​	3018​	9​	1961​	9​	185.00​	9​	2557​
11​		10​	3017​	10​	1936​	10​	145.00​	10​	2469​
12​		11​	3016​	11​	1903​	11​	250.00​	11​	1362​
13​		12​	3015​	12​	1866​	12​	155.00​	12​	2544​
14​		13​	3011​	13​	1859​	13​	155.00​	13​	1373​
15​		14​	3004​	14​	1853​	14​	185.00​	14​	2545​
16​		15​	3001​	15​	1823​	15​	195.00​	15​	2316​
17​		16​	2009​	16​	1790​	16​	240.00​	16​	2468​
18​		17​	2008​	17​	1828​	17​	235.00​	17​	542​
19​		18​	2007​	18​	1776​	18​	210.00​	18​	549​
20​		19​	2006​	19​	1753​	19​	175.00​	19​	2573​
21​		​							​
22​		​							​

Sheet: Sheet1

 

[Rick Rothstein]

Give this macro a try...

Sub InsertColumnsAddRowCounter()
  Dim col As Long, StartRow As Long, StartCol As Long, LastRow As Long, LastCol As Long
  StartRow = 2
  StartCol = 2
  LastRow = Cells(Rows.Count, StartCol).End(xlUp).Row
  LastCol = Cells(StartRow, Columns.Count).End(xlToLeft).Column
  For col = LastCol To StartCol Step -1
    Columns(col).Insert
    Range(Cells(StartRow, col), Cells(LastRow, col)) = Evaluate("ROW(1:" & LastRow - StartRow + 1 & ")")
  Next
End Sub

 

[Vishaal]

Thanks Rick Rothstein Sir Ji

its working fine...........