CODEWITHSUNDEEP
excelvba
Zacchini
Zacchini

Reputation: 143

Delete specified column VBA

Hi just trying to run a code on every sheet in a workbook. For some reason it wont delete the column as specified. I've also tried using Columns(“A”).Delete and that did not work either

Sub Format()

'Firstly convert all text cells into number cells for every sheet in workbook

For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = (r.Value) * 1
        ''Or test the values in the next column on the right
        'If IsNumeric(r) Then r.Offset(0,1).Value = (r.Value)*1
    Next r

    Rows("1:15").Delete
    Columns(“1”).Delete


Next WS

End Sub

Upvotes: 0

Views: 60

Answers (1)

braX
braX

Reputation: 11755

You aren't qualifying those references with the sheet... it should look like this:

WS.Rows("1:15").Delete
WS.Columns(1).Delete

If you don't do that, then it just assumes ActiveSheet each time it loops.

Notice also that you do not put the 1 for columns in quotes. The 1 is an index value, not a string value.

Also, the type of quotes you are using is wrong. Look closely:

WS.Columns(“A”).Delete  ' <--- these curly quotes will not work (they are not the same thing)

WS.Columns("A").Delete  ' <--- these straight quotes work

straight and curly quotes

Upvotes: 2

Related Questions