Excel macro needed for deleting column contents and column as well

[talktorajpatil]

Hi folks,

I have an excel sheet with multiple tabs and multiple columns with randon sequence on each tab.
I have two requirements -
1) I want to delete entire columns iteself from all the tabs of excel with value in 1st row as 'Select' or 'Start'
2) I would like to clear the contents (and not to delete column) with value in 1st row as 'Prompt'. (Value in 1st row should not be deleted)

I am novoice to VBA Macro for excel. Can anyone give me a complete macro for it?
Detailed help is appreciated.
Thanks in advance.

-Regards,
Raj Patil

 

[SteveO59L]

typically, how many columns are used on a sheet?

 

[talktorajpatil]

typically, how many columns are used on a sheet?

Aproximately 15 on each sheet. But this number is not fixed. But not more than 30.

 

[SteveO59L]

something along the lines of

Dim lColumn As Long
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Activate
lColumn = Cells(1, Columns.Count).End(xlToLeft).Column

For i = lColumn To 1 Step -1

If Cells(1, i).Value = "select" Or Cells(1, i).Value = "start" Then
Cells(1, i).EntireColumn.Delete
End If
Next i

For i = lColumn To 1 Step -1
If Cells(1, i) = "prompt" Then
Cells(1, i).EntireColumn.ClearContents
Cells(1, i) = "prompt"

End If
Next i

Next ws

 

[talktorajpatil]

Hi SteveO59L,

Thanks a lot for your help, works like boom
Only one thing, how can I add one more thing to it in order to make all text on all sheets in excel in black color?

-Regards,
Raj Patil

 

[Michael M]

Try

Dim lColumn As Long, ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.UsedRange.Font.ColorIndex = 1
lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
For i = lColumn To 1 Step -1
If Cells(1, i).Value = "select" Or Cells(1, i).Value = "start" Then
Cells(1, i).EntireColumn.Delete
End If
Next i
For i = lColumn To 1 Step -1
If Cells(1, i) = "prompt" Then
Cells(1, i).EntireColumn.ClearContents
Cells(1, i) = "prompt"
End If
Next i
Next ws

 

[talktorajpatil]

Thanks Michael for prompt reply
Sorry to bother you one more time, can we add something to delete all 'comments' added to all tabs in excel?
Also I am getting below error with your above code -

"Excel cannot complete this task with available resources. Choose less data or close other applications"

 

[Michael M]

Sub MM2()
Dim lColumn As Long, ws As Worksheet
For Each ws In Worksheets
ws.Activate
ws.UsedRange.Font.ColorIndex = 1
ws.Cells.ClearComments
lColumn = Cells(1, Columns.Count).End(xlToLeft).Column
For i = lColumn To 1 Step -1
If Cells(1, i).Value = "select" Or Cells(1, i).Value = "start" Then
Cells(1, i).EntireColumn.Delete
End If
Next i
For i = lColumn To 1 Step -1
If Cells(1, i) = "prompt" Then
Cells(1, i).EntireColumn.ClearContents
Cells(1, i) = "prompt"
End If
Next i
Next ws
End Sub

 

[talktorajpatil]

Thats fantastic,

Thanks a ton Michael and SteveO59L

 

[talktorajpatil]

Hi Folks,

After applying above macro, my excel file size is growing from 1.5 MB to ~40MB.
What can be done? Please help.

-Regards,
Raj Patil