VBA Code for Clear Contents in different sheets containing different rows and columns

[rush2rajen]

Hi All,
For clearing contents I had written a vba code but I had written separately for all the sheets as because different sheet contains different rows & column.

Is there any way ( vba code ) where I can run all of them together and my contents gets cleared.

Please help.
ThanksRaj

 

[chirayu]

Change code to this. When you run macro - you will only see "DELETE"

Private Sub DELETE1()
Worksheets("suven").Range("g2:G117").ClearContents
End Sub
Private Sub DELETE2()
Worksheets("suman").Range("f2:f197").ClearContents
End Sub
Private Sub DELETE3()
Worksheets("raj").Range("e2:e100").ClearContents
End Sub
Sub DELETE()

Call DELETE1
Call DELETE2
Call DELETE3

End Sub

 

[rush2rajen]

Hi Chirayu

Thanks a lot.
Just one question I am having 60 sheets so for all the sheets I need to write the private sub functions and then call delete for all the sheets.
or is there any other way to do it.

Kindly suggest.


Thanks Raj

 

[chirayu]

Am I right in saying you only need to clear the right most column of each sheet?

 

[rush2rajen]

Yes

 

[chirayu]

Delete all the code & use this instead

Sub ClearCols()

Dim LstRow As String
Dim ws As Worksheet
Dim EndCol As String

For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    LstRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A1").End(xlToRight).Offset(1, 0).Select
    EndCol = Split(ActiveCell.Address, "$")(1)
    Range(EndCol & "2:" & EndCol & LstRow).ClearContents
Next

End Sub

 

[Chihiro]

Instead of select, you can do it like this.

Sub DelLastCol()
Dim ws As Worksheet
Dim lRow As Long

For Each ws In ThisWorkbook.Worksheets
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    ws.UsedRange.End(xlToRight).Resize(lRow, 1).Offset(1, 0).ClearContents
Next ws
End Sub

 

[Marc L]

Hi !

Another way just respecting TBTO rule :​

Sub ClearLastColumn()
         Dim Ws As Worksheet
         Application.ScreenUpdating = False
    For Each Ws In ThisWorkbook.Worksheets
        With Ws.UsedRange.Columns
            .Item(.Count).Offset(1).ClearContents
        End With
    Next
         Application.ScreenUpdating = True
End Sub

rush2rajen, reminder :
Do you like it ? So thanks to click on bottom right Like !
This applies for any post that helped you …
​