0

Ive used the following sub procedure to clear an input once a reset button is pressed however it is clearing all of the text on my worksheet. I have done this with multiple other inputs and not had this problem so I am unsure why this is happening.

Sub ClearPremisesDataEntry()
    Dim InputSheet As Worksheet
    Dim InputRange As Range
    
    Set InputSheet = Worksheets("Input")
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'The range of cells that will be copied from the input sheet are set to InputRange
    Set InputRange = InputSheet.Range("PremisesDataEntryClear")

    'Clears the input cells ready for new a new input
    With InputSheet
      On Error Resume Next
         With InputRange.Cells.SpecialCells(xlCellTypeConstants)
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
    
    Application.EnableEvents = True

End Sub

Sub ClearCommunicationDataEntry()

    Dim InputSheet As Worksheet
    Dim InputRange As Range
    
    Set InputSheet = Worksheets("Input")
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    'The range of cells that will be copied from the input sheet are set to InputRange
    Set InputRange = InputSheet.Range("CommunicationDataEntryClear")

    'Clears the input cells ready for new a new input
    With InputSheet
      On Error Resume Next
         With InputRange
              .ClearContents
              Application.GoTo .Cells(1) ', Scroll:=True
         End With
      On Error GoTo 0
    End With
    
    Application.EnableEvents = True

End Sub
Dan
  • 13
  • 6
  • https://stackoverflow.com/questions/40537537/range-specialcells-clearcontents-clears-whole-sheet-instead – BigBen Jul 11 '22 at 13:33
  • Side note, but `With InputSheet` is redundant and can be removed. – BigBen Jul 11 '22 at 13:35
  • Thank you, I am slightly confused though as i have another sub nearly exactly the same for a different range which is also only one cell and this works? Is there a reason this might happen? I've added the code to the post. – Dan Jul 11 '22 at 13:37
  • If it's a single cell, you shouldn't be using `SpecialCells` (which, you aren't, in `ClearCommunicationDataEntry`. – BigBen Jul 11 '22 at 13:38
  • Ok, was it just a coincidence that this worked for the other range then? – Dan Jul 11 '22 at 13:39
  • The range ```CommunicationDataEntryClear``` is a single cell though? – Dan Jul 11 '22 at 13:40
  • Right, and is `"PremisesDataEntryClear")` a single cell? – BigBen Jul 11 '22 at 13:41
  • 1
    Yes, don't worry I now see my mistake, I did not use special cells for ```CommunicationDataEntryClear``` – Dan Jul 11 '22 at 13:42

0 Answers0