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.
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.
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.
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
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines