Delete any data/table exists in a given range.

[inddon]

Hello There,

I have a given range (eg. C10:G40) where the data will be pasted. Before I could do that I am checking if that given range is empty/available. If yes then paste the data.
If the given range is not empty then I would like to do delete the data:

1.If there are any normal data.
2. If it includes any table(s), or any part of the table columns in that range.

How could this be achieved using VBA?

Regards,
Don

 

[vletm]

inddon
Do You really mean to delete that data ?
or
would be clear contents ( ~ visible data ) from that range okay?
What is any normal data?
If delete, then there won't be any table anymore ...

 

[inddon]

Hi vletm,

Thank you for your response.

I suppose to clear contents of that range, and if there exists any table(s) then delete the table(s) as well (Without deleting the actual physical rows or columns)

Regards,
Don

 

[vletm]

inddon
This sample should do Your expected checking and deleting with given range.

 

[inddon]

inddon
This sample should do Your expected checking and deleting with given range.

Thank you @vletm. I will adapt your solution.

Regards,
Don

 

[inddon]

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.

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

 

[vletm]

inddon
Your original given range was ... I have a given range (eg. C10:G40)
Without Your realistic sample Excel-file,
I could add some lines to my code.
... Freezing-effect could avoid eg with DoEvents

 

[inddon]

inddon
Your original given range was ... I have a given range (eg. C10:G40)
Without Your realistic sample Excel-file,
I could add some lines to my code.
... Freezing-effect could avoid eg with DoEvents

Hello @vletm
Thank you for the DoEvents tip. It did improve the performance

I tweaked your code to first check if the cursor is in a table. Then look for special cells, after clearing check if the range is clear then exit for as below. Somehow it is working much better. Thanks again for your direction.

Sub ClearRange(p_Range As String)
  Dim cel As Range, t As Variant
  On Error Resume Next
   
  If WorksheetFunction.CountA(Range(p_Range)) = 0 Then Exit Sub
  With ActiveSheet
    'incase the cursor position is in the table, then this does delete the table
    t = Range(p_Range).Cells.ListObject.Name
    If t <> vbNullString Then
      .ListObjects(t).Delete
      DoEvents
    End If
    
    For Each cel In Range(p_Range).SpecialCells(xlCellTypeConstants)
      DoEvents
      t = Empty
      t = cel.ListObject.Name
      If Err.Number = 0 Then
        .ListObjects(t).Delete
      Else
        Err.Clear
        cel.Value = vbNullString
      End If
            
      If WorksheetFunction.CountA(Range(p_Range)) = 0 Then Exit For
    Next cel
  
    'For Constants
    .Range(p_Range).Cells.SpecialCells(xlCellTypeConstants).ClearContents
    .Range(p_Range).Cells.SpecialCells(xlCellTypeConstants).ClearNotes
    .Range(p_Range).Cells.SpecialCells(xlCellTypeConstants).ClearComments
    .Range(p_Range).Cells.SpecialCells(xlCellTypeConstants).ClearFormats
   
    'For any Formaulas
    .Range(p_Range).Cells.SpecialCells(xlCellTypeFormulas).ClearContents
  End With
End Sub

 

[vletm]

inddon
Your thread is Delete any data/table exists in a given range.
Where You've connection with a given range and the cursor position? I tweaked your code to first check if the cursor is in a table.
But, it's good that You got it working better.