CODEWITHSUNDEEP
excelvbaloops
martyna s
martyna s

Reputation: 1

Deleting columns with given header/name

I'm trying to delete columns named "delete" in first row starting from column B. The first column is named differently, "hide", because here I placed some commands which perform changes on rows (not included in below code), hence it is important to start deleting the columns from B.

The code I have deletes the columns as follows:

A (no deleting must stay), B (deleted), C (not deleted), D (deleted), E (not deleted), even though I named all from B to E with "delete".

Sub deleting()
      
    Dim lcolumn As Long ' column
    Dim j As Long
    
    Dim sh As Worksheet, shs As Sheets
    Dim wkbTarget As Workbook
    
    For Each sh In ActiveWorkbook.Worksheets ' here's a loop through each sheet
    
        sh.Select
        With ActiveSheet
    
            lcolumn = .Cells(1, .Columns.Count).End(xlToLeft).Column
    
            For j = 1 To lcolumn
                If .Cells(1, j).Value = "delete" Then 'here is something wrong...
                    sh.Columns(j).Delete Shift:=xlShiftToLeft
                End If
            Next j
    
        End With
    
    Next sh
End Sub

Upvotes: 0

Views: 150

Answers (1)

Warcupine
Warcupine

Reputation: 4640

You need to iterate backwards, you are deleting a column which then shoves all columns to the left. So you are skipping one column with every column deleted.

So instead of

For j = 1 To lcolumn
    If .Cells(1, j).Value = "delete" Then 'here is something wrong...
        sh.Columns(j).Delete Shift:=xlShiftToLeft
    End If
Next j

You want

for j = lcolumn to 2 step -1 'to 2 because you want to skip column A
   If .Cells(1, j).Value = "delete" Then 
       sh.Columns(j).Delete Shift:=xlShiftToLeft
   End If
Next j

Upvotes: 2

Related Questions