Deleting data from excel sheet

[Ipek]

I have a macro that runs and populate sheet2 with data from database. I want to clear contents of sheet2 before loading data to it. I want to clear contents of sheet2 from A2 cell up to the last row in the sheet. how do I code this in VBA 2007, thanks

 

[Somendra Misra]

Hi Ipek,

Try below code:

Sub ClearContent()
 
Dim ws As Worksheet
Dim lastrow As Long
 
Set ws = ThisWorkbook.Worksheets("Sheet2")
 
Application.ScreenUpdating = False
With ws
 
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For i = 2 To lastrow
  ws.Range("A" & i).ClearContents
Next i
Application.ScreenUpdating = True
End Sub

Regards,

 

[Somendra Misra]

Thread Moved to VBA Forum.

Regards,

 

[SirJB7]

Hi, Ipek!

I suggest you to replace this part of Somendra Misra's code:

For i = 2 To lastrow
  ws.Range("A" & i).ClearContents
Next i

with this:

  ws.Range("A2:A" & lastrow).ClearContents

It'll run once and avoid the loop for each row. In large ranges it's highly noticeable.

But that only clears column A, so if you want to clear all the columns use this:

  With ws
    If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
  End With

Regards!