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.
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
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.