VBA Delete Columns

[davidlim]

hi guys,


am wondering for a way to remove certain columns from a large excel table.


for example, i'm trying to remove Column AG, and Columns AA:AD.


I tried the below vba, it doesnt work (getting "Type Mismatch" message)


Sub Delete()

Columns("AG, AA:AD").Delete

End Sub


any ideas?


thanks!

 

[Marc L]

Hi !


Avoid to call a Sub Delete 'cause it's a reserved word …

[pre]

Sub DelCol
Union(Columns("AA:AD"), Columns("AG")).Delete
end Sub

[/pre]

 

[davidlim]

hi marc,


I'm getting error message "400"

 

[davidlim]

and btw, I'm using Excel 2007 (dont ask me why)

 

[sdsurzh]

Marc thanks for that code.

 

[mahaveer]

Sub DelCol

sheet1.activate

Union(Columns("AA:AD"), Columns("AG")).Delete

end Sub


now use the above code for sheet1.....


Regards

CA Mahaveer Somani

 

[davidlim]

@mahaveer,


works flawlessly! Thanks!

 

[davidlim]

@mahaveer,


i encountered an issue with the vba.


if i use it on a new sheet with few rows, it works.


i tried to use it on large excel sheet (700K+ rows), i will get Error 400.


any idea/solution?


thanks!

 

[SirJB7]

Hi, davidlim!


Does this helps?

http://www.add-ins.com/support/microsoft-visual-basis-error-400.htm

http://www.mrexcel.com/forum/excel-questions/1721-visual-basic-error-400-a.html


Regards!