Loop for all the worksheet to delete column

[mohan08]

Hi All,

Request help on completing the macro.

Intention of the macro retain 4 coulmns on the all the sheets available in the wook book.
macro is able to delete the coulmns but having trouble in looping to other worksheets.

also help for deleting rows from 1 to 9 and only to retain rows containing "product List" Available on row 4

To delete all the rows and columns after table i.e A10:D14.

if feasible can make user to choose the row number for deleting column,
instead of "Cells(10, x).Value" called out in the code.

Mohan

 

[Monty]

Dear Mohan.

Please find amended macro..
Your macro works well a small change.. shown in bold..please check and let me know for further assistances

Sub DeleteCol()
Dim WS As Worksheet 'Declare as worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'Dim WS As Object 'Declared as object which is not correct

Set r = ActiveSheet.UsedRange.Resize(1)

LC = r(r.Count).Column
For Each WS In ActiveWorkbook.Sheets
WS.Select 'Selecting worksheet to see the looping thought work sheets clearly.
For x = LC To 1 Step -1

    If Cells(10, x).Value <> "Brand" And Cells(10, x).Value <> "Launch Channel" And Cells(10, x).Value <> "EffectiveDate" And Cells(10, x).Value <> "ItemCode" Then
        Columns(x).EntireColumn.Delete
    End If
Next x
Next WS
End Sub

.

 

[mohan08]

Thanks Monty, Modified code works fine.
Would also like to check on Deleting rows from row 1 -9 and also incase of column header start from row 3 instead of row 10 for which it deletes all the records. Is there a way to make the user select columns instead of declaring in the code

 

[mohan08]

Help required on the above two modification.