VBA to clear contents of specific cells

[Rubber Soul]

Hi everyone,

Long time reader, first time poster.

I am quite new to VBA, but I am working on a spreadsheet based on the code from this video:

My aim is to have a workbook with one sheet which I use as a database and another sheet for an archive. I want to be able to archive entire rows from the database via a dropdown (with the options Ongoing/Archive) in column A of the database. Here is the database.

Here is the code I am using. I have amended the code from the video to clear the contents of the row rather than delete it entirely.

Sub MoveBasedOnValue()
    Dim xRg As Range
    Dim xCell As Range
    Dim A As Long
    Dim B As Long
    Dim C As Long
    A = Worksheets("Database").UsedRange.Rows.Count
    B = Worksheets("Archive").UsedRange.Rows.Count
    If B = 1 Then
        If Application.WorksheetFunction.CountA(Worksheets("Archive").UsedRange) = 0 Then B = 0
    End If
    Set xRg = Worksheets("Database").Range("A1:A" & A)
    On Error Resume Next
    Application.ScreenUpdating = False
    For C = 1 To xRg.Count
        If CStr(xRg(C).Value) = "Archive" Then
            xRg(C).EntireRow.Copy Destination:=Worksheets("Archive").Range("A" & B + 1)
            xRg(C).EntireRow.ClearContents
            If CStr(xRg(C).Value) = "Archive" Then
                C = C - 1
            End If
            B = B + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

I am also using code to automate the process, so when you select Archive in column A the entire row will automatically be copied to the Archive tab and the row in the database will be cleared.

My problem is that I do not want to clear the entire row, I only want to clear certain cells from the database, and leave other cells there as static cells. So, when I select Archive in (for example) cell A4 I still want it to copy the entire row to the archive, but I want the database tab to look like this, with only cells A4, C4, D4 and E4 having been cleared.

I have tried to use ranges but I keep running into errors, and I'm not really sure how to proceed because this is all quite new to me!

If anyone is able to help it would be much appreciated, and please let me know if you need more information!

 

[Fluff]

How about

        If CStr(xRg(c).Value) = "Archive" Then
            xRg(c).EntireRow.Copy Destination:=Worksheets("Archive").Range("A" & b + 1)
            xRg(c).ClearContents
            xRg(c).Offset(, 2).Resize(, 3).ClearContents
            If CStr(xRg(c).Value) = "Archive" Then
                c = c - 1
            End If
            b = b + 1
        End If

 

[Rubber Soul]

Amazing!! Thank you so much, this is absolutely perfect.

I wonder, is it possible to explain what Offset and Resize are doing here - would be really useful when I implement this in a real worksheet, where I may need to clear different cells.

Thanks again!

 

[Rubber Soul]

Actually, all good, figured it out haha

 

[Fluff]

Glad to help & thanks for the feedback

 

[Rubber Soul]

Glad to help & thanks for the feedback

Hi again!

Sorry - if you can bear to answer another question, it would be much appreciated!

I need to clear the contents but make sure that formulas in the cells remain intact. I know I need to use SpecialCells(xlCellTypeConstants), but am struggling to work out where to put it in the code.

 

[Fluff]

You cannot clear the contents & leave the formula.

 

[Rubber Soul]

You cannot clear the contents & leave the formula.

It seems to be working with this line of code:

xRg(C).Offset(, 8).Resize(, 16).SpecialCells(xlConstants).ClearContents