CODEWITHSUNDEEP
vbaexcel
Dr_Umut
Dr_Umut

Reputation: 23

Delete columns in Excel depending on a specific cell

I have a very important question about deleting some columns containing unnecessary data in excell and shift the rest of the columns to left after deletion. I have tried some VBA macro that i have found in a small google search and that macro, the link that i have found that macro was http://analysistabs.com/vba/delete-columns-with-specific-data-excel-macro-example-code/

The problem is when i have tried to use this macro with the instruction of the writer it is not working and i don't have the knowledge for understanding why it is not working or not. Thats why i want if you guys can give me some advise for dummies version of writing a Macro for deleting any column if the column contains in cell 170 "0" and leave the column undeleted if the column contains and other number except "0" on cell 170 of the column. i gave an example of my excell documents as a picture below. i want the columns remain with me with green circled and delete the columns that i exampled with red circles.

This Matrix contains "2000" columns between column A and BXX and "170" rows with sample codes in the 1st column and result code in the 1st row. the 170th row contains the total of the column and i want to delete this column if the column sum is "0"

Thanks for your answers.. Best Regards.

Upvotes: 1

Views: 2073

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Try this macro:

Sub ColumnKiller()
    Dim Nrow As Long, Nend As Long, i As Long
    Nrow = 170
    Nend = Cells(Nrow, Columns.Count).End(xlToLeft).Column
    For i = Nend To 1 Step -1
        If Cells(Nrow, i) <> "" Then
            If Cells(Nrow, i) = 0 Then
                Cells(Nrow, i).EntireColumn.Delete
            End If
        End If
    Next i
End Sub

Upvotes: 2

Related Questions