Simple macro problem 1

[kragster]

Hi,
I am trying to make a simple macro, but I'm stuck and need some advice. The macro should go though the cells of a row and when the cell contains specific data it should delete the entire column. This is my code so far (probably not the right way of doing it):

Sub delColumn()
Range("A12").Select

Do Until ActiveCell.Value = ""
    If ActiveCell.Value = "C1" Then
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    
    ElseIf ActiveCell.Value = "C2" Then
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
        
    ElseIf ActiveCell.Value = "C3" Then
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    End If
    ActiveCell.Offset(0, 1).Range("A1").Select
Loop
End Sub

Problem is that it stops after deleting the first column and I think I know what the problem is. After deleting a column, the activeCell is not on the starting row anymore and thus ActiveCell.Offset(0, 1).Range("A1").Select does not move the active cell in row 12 anymore. Can anyone help med fix this?

Regards
Kragh

 

[kragster]

Ok I solved it the following way:

Sub delColumn()
Range("A12").Select

Do Until ActiveCell.Value = ""
    If ActiveCell.Value = "C1" Then
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(11,-1).select


    ElseIf ActiveCell.Value = "C2" Then
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(11,-1).select

        
    ElseIf ActiveCell.Value = "C3" Then
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Delete Shift:=xlToLeft
    ActiveCell.Offset(11,-1).select
    End If
    ActiveCell.Offset(0, 1).Range("A1").Select
Loop
End Sub

This is absolutely not the prettiest way of doing this, but it works. If you have any suggestions on how to do this a better way, then please enlighten me.

 

[SkipVought]

Hi,

Sub delColumn()
    Dim iCol As Integer, lRow As Long
    
    iCol = 1
    lRow = 12
    
    Do Until Cells(lRow, iCol).Value = ""
        Select Case Cells(lRow, iCol).Value
            Case "C1", "C2", "C3"
                Cells(lRow, iCol).EntireColumn.Delete Shift:=xlToLeft
                lRow = lRow + 11
                iCol = iCol - 1
        End Select
        iCol = iCol + 1
    Loop
    
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[kragster]

Thanks skip. You allways got the solution!