Delete specified column VBA

Question

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

Columns("A") would work, as long as you also specified the worksheet: ws.Columns("A").Delete — BigBen
– BigBen, Commented Jan 15, 2020 at 3:33

braX · Accepted Answer · 2020-01-15 04:13:29Z

2

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

edited Jan 15, 2020 at 4:13

answered Jan 15, 2020 at 3:17

braX

11.9k5 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete specified column VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related