inddon
This sample should do Your expected checking and deleting with given range.
Hello
@vletm
Your solution worked so far very good (below mentioned with a slight modification)
Recently, there came quite a large range. The Excel file was freezing (Not Responding). I searched a lot for the possible options and added some lines of code, still the freezing issue remained with large range.
Mostly in the worksheet I have excel tables.
Just a thought, is it possible to get a list of tables and check if these tables exists in this given range and delete them and then rr.Range(p_Range).ClearContents ?
Or any other working example, you think could work faster.
Thank you for taking the time. Look forwad for your reply.
Code:
Sub ClearRange(p_Range As String)
Dim rr As Range, cel As Variant, a As Variant, t As Variant
On Error Resume Next
With ActiveSheet
Dim lcounter As Long
Set rr = .Range(p_Range)
rr.Range(p_Range).ClearContents '---------------New code added-
For Each cel In rr.Cells
'---------------------New code added-----------------------------------
'To avoid screen freezing (Not Responding)
'keep checking if the range is free. if yes then exit for.
If WorksheetFunction.CountA(Range(p_Range)) = 0 Then Exit For
lcounter = lcounter + 1
If lcounter = 100 Then lcounter = 0: Call UserFormRepaint '(Repaint and DoEvents)
'--------------------New code added------------------------------------
a = cel.Address
If cel.Value <> Empty Or Range(a).Value <> vbNullString Then
cel.Value = Empty
Range(a).ClearComments
Range(a).ClearNotes
Range(a).ClearFormats
End If
t = Empty
t = cel.ListObject.Name
If Err.Number = 0 Then
.ListObjects(t).Delete
Else
Err.Clear
End If
Next
End With
End Sub
Regards,
Don