VBA to clear contents of specific cells

Rubber Soul

New Member
Joined
Aug 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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.

Picture1.png


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.

Rich (BB code):
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.

Picture2.png


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!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
        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
 
Solution

Rubber Soul

New Member
Joined
Aug 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad to help & thanks for the feedback
 

Rubber Soul

New Member
Joined
Aug 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,892
Office Version
  1. 365
Platform
  1. Windows
You cannot clear the contents & leave the formula.
 

Rubber Soul

New Member
Joined
Aug 4, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,005
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top