VBA : deleteing the information from sheet

Question

I'm wondering if there is more efficient way to clean my sheet. My code takes a long time to run (I have 5000 observations ):

Dim Num_Ligne As Long
 Num_Ligne = 8
     
 While Cells(Num_Ligne, 3) <> ""
  ActiveSheet.Cells(Num_Ligne, 3).Value = ""
  ActiveSheet.Cells(Num_Ligne, 4).Value = ""
  ActiveSheet.Cells(Num_Ligne, 5).Value = ""
  ActiveSheet.Cells(Num_Ligne, 6).Value = ""
  ActiveSheet.Cells(Num_Ligne, 7).Value = ""
  ActiveSheet.Cells(Num_Ligne, 8).Value = ""
  ActiveSheet.Cells(Num_Ligne, 9).Value = ""
  ActiveSheet.Cells(Num_Ligne, 10).Value = ""
    Num_Ligne = Num_Ligne + 1
Wend

Thank you for your help !

Answer 1

Clear Contents of a Range

Requirements

• Clear the contents of a range.

OP's Solution

• Loop through each cell and test if it is empty. Then loop through each column and clear its contents. For 5000 records it tests 5000 times a cell if it is blank and clears the contents of a cell 40.000 times which takes about 10s on my machine.

Solution

• Create a reference to all those cells and clear the contents in one go.
• The first solution is a 'flavor' of BigBen's solution in the comments which is widely used (popular) and is based on the End property. This (my) solution uses Resize ('flavor').
• The second solution is a more reliable version that uses the Find method which may only fail if the worksheet is filtered. The End solution will additionally fail if the last cell on the worksheet is occupied (highly unlikely), if the last row is less than the specified first row (unlikely), and if there are hidden rows (unlikely).
• The third solution is kind of a study of the second solution.
• For even more insights, study the legendary Siddharth Rout's answer to Error in finding last used cell in Excel with VBA.

The Code

Option Explicit

Sub RangeClearContentsEnd()
    
    Const Cols As String = "C:J"
    Const FirstRow As Long = 8
     
    With ActiveSheet.Columns(Cols)
        Dim LastRow As Long
        LastRow = .Columns(1).Cells(.Rows.Count).End(xlUp).Row
        .Rows(FirstRow).Resize(LastRow - FirstRow + 1).ClearContents
    End With
    
End Sub

Sub RangeClearContentsFind()
    
    Const Cols As String = "C:J"
    Const FirstRow As Long = 8
     
    With ActiveSheet.Columns(Cols).Rows(FirstRow)
        Dim cel As Range
        Set cel = .Resize(.Worksheet.Rows.Count - FirstRow + 1, 1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If Not cel Is Nothing Then
            .Resize(cel.Row - FirstRow + 1).ClearContents
        End If
    End With
    
End Sub

Sub RangeClearContentsFindStudy()
    
    Const Cols As String = "C:J"
    Const FirstRow As Long = 8
     
    ' Define the first row (range) of the range.
    Dim rrg As Range: Set rrg = ActiveSheet.Columns(Cols).Rows(FirstRow)
    Debug.Print "First Row of the Range    : " & rrg.Address(0, 0)
    ' Define the range referring to the first column from
    ' the specified first row to the bottom-most row of the worksheet.
    Dim frg As Range
    Set frg = rrg.Resize(ActiveSheet.Rows.Count - FirstRow + 1, 1)
    Debug.Print "First Column to the Bottom: " & frg.Address(0, 0)
    ' Attempt to find the last non-empty cell in it.
    Dim lCell As Range
    Set lCell = frg.Find("*", , xlFormulas, , , xlPrevious)
    ' Validate the last non-empty cell.
    If Not lCell Is Nothing Then
        Debug.Print "Last Non-Empty Cell:        " & lCell.Address(0, 0)
        ' Define the range from the first specified row
        ' to the last non-empty cell's row.
        Dim drg As Range: Set drg = rrg.Resize(lCell.Row - FirstRow + 1)
        Debug.Print "Delete Range:               " & drg.Address(0, 0)
        ' Clear its contents.
        drg.ClearContents
    Else
        Debug.Print "Nothing cleared."
    End If
    
End Sub

Answer 2

The Requirements: To clear the contents of a range.

The Data: The data involved contains a mix of cells (i.e:, some cells have content, others are empty), as could be understood from this line in the question code While Cells (Num_Ligne, 3) <>"".

The figure below represents a sample of the data. There we have cells with a constant value (X), cells with formulas (filled with a pattern), and all other cells are empty. In this sample data the Last Row is the Row 26.

Now let's look at some of the methods to obtain the last row in line with the data involved.

End(xlUp).Row method: In this case, I would suggest not using this method unless it is applied to each of the columns involved, but then it won't be practical.
When applied only to the first column, it returns row 25, instead of row 26.

Find method: In this case, this method will only be effective if it is applied to the entire range.
When applied only to the first column, it also returns row 25.
When applied to the entire range [C:J] it will return the correct row 26.
However, if the last time the Find method was utilized the SearchOrder applied was xlByColumns, then when we run our procedure the method will return row 25, if the SearchOrder was not set to xlByRows in our procedure.
Bear in mind that the LookIn, LookAt, SearchOrder and MatchByte* parameters are saved each time the Find method is applied, therefore these parameters should always be included (*if applicable) to ensure the expected return.

UsedRange: This is one of the few cases where this worksheet property could be used. As in this case, the objective is to set a range from an initial row to the last row with contents of specific columns and then clear the contents of that range. Actually utilizing the UsedRange simplifies the code a lot.

With ActiveSheet
    Range(.Rows(8), .Rows(.UsedRange.SpecialCells(xlCellTypeLastCell).Row)).Columns("C:J").ClearContents
End With

Last Row? However, each problem has its own peculiarities, and for this particular problem we should also ask ourselves the question:

• In this case, is the last row really necessary?

I think the answer is no. So let's simplify things even more.

With ActiveSheet
    Range(.Rows(8), .Rows(.Rows.Count)).Columns("C:J").ClearContents
End With

Answer 3

You have options:

Sub test()

Dim Num_Ligne As Long
    
Num_Ligne = 8
      
While Cells(Num_Ligne, 3) <> ""
   'ActiveSheet.Cells(Num_Ligne, 3).EntireRow.Clear
   ActiveSheet.Range("C" & Num_Ligne & ":J" & Num_Ligne).Clear
   Num_Ligne = Num_Ligne + 1
Wend

End Sub