Clear data cells with cDataSet

Hopefully you will have read How to use cDataSet to see how to abstract data from your sheet and taken a look at the cDataSet Model to see which methods and properties are available. I almost always use cDataSet when accessing sheet data from either VBA .

I also use Data manipulation Classes in Google Apps Script when working with Google Docs. Firstly you can write more or less the same code for each platform, but also there are plenty of short cuts you can use to do complicated things. All the libraries are in cDataSets.xlsm you need are available from Downloads.

You will also find a link to the Google Apps Script libraries there too.

Clearing data from the first table in a worksheet

Quite often, you need to clear away existing data from a table, but leave the headings intact. Here’s how to do that with cDataSet.

VBA

Public Sub clearCells()
    Dim dSet As New cDataSet
    With dSet.populateData(wholeSheet("eSimResource"))
        If Not .Where Is Nothing Then .Where.ClearContents
    End With
End Sub

function clearCells() {
    var ds = new cDataSet().populateData(wholeSheet("eSimResource"));
    if (ds.where()) ds.where().clearContent();
}

Walkthrough

The populate method of cdataset finds the first table on the given sheet. A useful property is .where, which is a the spreadsheet range that the data for that table is found in. If there is no data (just headings), then this property will be Nothing (or null in GAS), so test this and clear the data if there is some. cDataSet will take care of figuring out the size of the table (bounded by blank rows/columns, so anything else on the sheet will remain intact.

Clearing data from one of many tables in a worksheet

Now lets say that we have a sheet with a number of tables (see How to use cDataSet for how these are often used as parameter tables), and you don’t know exactly which row that table starts on  but you want to clear it and leave everything else intact. In the example below we want to clear the contents of the second table.

A small change to the previous example is all thats needed.

VBA

Private Sub clearParameters()
    Dim dSet As New cDataSet
    With dSet.populateData(wholeSheet("f1Drivers"), , , , "season")
        If Not .Where Is Nothing Then .Where.ClearContents
    End With
End Sub

Google Apps Script

function clearParameters() {
var ds = new cDataSet().populateData(wholeSheet(“f1Drivers”),
undefined, undefined,undefined ,”season” );
if (ds.where()) ds.where().clearContent();
}
[/sourcode] Walkthrough

In this we just need to specify the top right hand column label as the start point of the table (“season”) and the .where property of the dataset will refer to that second table and clear just that.

Take a look at One Liners for more tips like this. In the meantime why not join our forum, follow the blog or follow me on twitter to ensure you get updates when they are available.