PDA

View Full Version : Insert empty columns



plus_stick
07-03-2012, 02:13 AM
Hi all.
I have tables in Excel.
The names of the columns are on a separate row, such as: VA001, VA002, VA003 ... VA038, etc.
But there are cases where some names are missing, for example VA001, VA002 and then VA004.
Different ways in different tables.
I need to add empty columns in place of each missing number.
I have a code that does everything perfectly.
But the code works when the range of column names starts from the first column.
And I have a range starting from the fourth or fifth column, it does'n matter.
Example in attachment.
Please. Help me find a solution. Thanks in advance.
PS. Sorry for my english. I hope that everything is clear.

snb
07-03-2012, 06:57 AM
Sub snb()
sn = Cells(1).CurrentRegion.Rows(1)

For j = UBound(sp, 2) To 1 Step -1
Columns(Val(Mid(sp(1, j), 3))) = Columns(j).Value
Columns(j).ClearContents
Next
End Sub

plus_stick
07-03-2012, 09:07 PM
Thanks for your reply, but it desn't solve the problem.
At runtime in any situation, I've got an error - "Run-time error 13. Type mismatch"
Please, tell me what to fix to make it work?

snb
07-04-2012, 12:01 AM
You had to remove the typo first.
After analysing this utterly simple code you could have fixed it yourself easily.



Sub snb()
sn = Cells(1).CurrentRegion.Rows(1)

For j = UBound(sn, 2) To 1 Step -1
Columns(Val(Mid(sn(1, j), 3))) = Columns(j).Value
Columns(j).ClearContents
Next
End Sub