Vba to Clear Cell Content But Retain The Formatting With Sheet Name

[muhammad susanto]

hi all...
i,m looking formula code to clear content from a sheet with name sheet "Register" cause i have several sheets
and only clear data in specific cell A2 : J30 but keep Retain The Formatting
i found 2 code but i don't know to modified

Sub sbClearEntireSheetOnlyData()
Sheets("Register").Cells.ClearContents
End Sub

and

Sub ClearWorksheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If Right(ws.Name, 4) = "register" Then ws.Cells.ClearContents
Next
End Sub

or maybe another code

any helping, appreciated with greatly.

.susanto

 

[Jeffrey Mahoney]

ClearContents only removes the values and does not change the cell formatting. The code below will remove the values from the range you provided on sheet "Register"

Sub sbClearEntireSheetOnlyData()
Sheets("Register").Range("A2:J30").ClearContents
End Sub

 

[muhammad susanto]

ClearContents only removes the values and does not change the cell formatting. The code below will remove the values from the range you provided on sheet "Register"

Sub sbClearEntireSheetOnlyData()
Sheets("Register").Range("A2:J30").ClearContents
End Sub

hi Jeffrey thank you, but can that code adding script Undo after running your code

 

[Jaafar Tribak]

hi Jeffrey thank you, but can that code adding script Undo after running your code

Undo is lost

 

[muhammad susanto]

how to make code above running in active sheet/current sheet not only specific name sheet and show message box " enter your range that your clear...." e.g. C2:F30 or D3:J100 etc..

 

[muhammad susanto]

hi..
how to modified that code that work in active sheet & can show box "Enter your range to clear.."

Sub sbClearEntireSheetOnlyData()
Sheets("Register").Range("A2:J30").ClearContents
End Sub

 

[muhammad susanto]

i have trying to make code like this

Sub clearcontains()
 Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
        Set wkbCrntWorkBook = ActiveWorkbook
          Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select range", Title:="Range", Default:="", Type:=8).ClearContents
            End Sub

but i don't know how to add code/statement "clear contents" and keep retain formula & formatting

 

[muhammad susanto]

hi man..
i have do and get this code

Sub clearcontains()
 Dim wkbCrntWorkBook As Workbook
    Dim wkbSourceBook As Workbook
    Dim rngSourceRange As Range
        Set wkbCrntWorkBook = ActiveWorkbook
          Set wkbSourceBook = ActiveWorkbook
            Set rngSourceRange = Application.InputBox(prompt:="Select range", Title:="Range", Default:="", Type:=8).SpecialCells(xlCellTypeConstants).ClearContents
                   End Sub

but after running code in the last moment show message "object required"
how to don't disappear/remove it that message.