The subroutine SpreadsheetResize can be used to programmatically resize a specific spreadsheet (or the active spreadsheet) by simply referencing the spreadsheet and entering in its new dimensions.

The first parameter is the target spreadsheet; if this is left blank, the active spreadsheet will be assumed (note, if there are no active spreadsheets, the active data set will be used). The second parameter is the new number of cases, and the third parameter is the new number of variables; if either of these parameters are left blank, the default value of ten will be assumed.

Copy and paste the following into your macro (outside of the main sub) to utilize SpreadsheetResize:

'Include this in your macro as a separate
function

Sub
SpreadsheetResize( _

Optional
ByRef
s As
Spreadsheet = ActiveSpreadsheet _

,Optional CaseNumber As Integer = 10 _

,Optional VarNumber As Integer = 10)

'pass the target spreadsheet, the desired number of cases

', and desired number of variables. If no spreadsheet is

'indicated then the active spreadsheet is used (if there

'is no active spreadsheet, then the active data set is

'used). If no case or variable number is specified, then

'the default of ten will be used.

'Note, the spreadsheet's original data will be

'preserved (unless the spreadsheet is shrunk, in

'which case some data will be truncated).

On
Error GoTo ResetTarget

'verify that the requested case/vars

'number are above zero

If Not(CaseNumber
> 0 And VarNumber
> 0) Then

MsgBox
"Invalid number of cases or variables"

Exit Sub

End If

Dim CaseCount As Integer,
VarCount As Integer,
_

Difference As Integer

CaseCount = s.Cases.Count

VarCount = s.Variables.Count

'cases resizing

'more cases are needed

If CaseNumber > CaseCount Then

Difference = CaseNumber - CaseCount

For i = 0 To
(Difference -1)

s.AddCases(CaseCount,1)
'add the cases

CaseCount = CaseCount + 1

Next i

'else less
cases are needed

ElseIf CaseCount > CaseNumber Then

Difference = CaseCount - CaseNumber

For i = 0 To
(Difference - 1)

'delete
cases

s.DeleteCases(CaseCount,CaseCount)

CaseCount = CaseCount - 1

Next i

End If

'variables

'more variables are needed

If VarNumber > VarCount Then

Difference = VarNumber - VarCount

For i = 0 To
(Difference -1)

'add variables

s.AddVariables("",VarCount,1,,,,,)

VarCount = VarCount + 1

Next i

'else less
variables are needed

ElseIf VarCount > VarNumber Then

Difference = VarCount - VarNumber

For i = 0 To
(Difference - 1)

'delete
variables

s.DeleteVariables(VarCount,VarCount)

VarCount = VarCount - 1

Next i

End If

Exit Sub

'no active spreadsheet found,

'search for the active spreadsheet

ResetTarget:

Dim SpreadsheetCount As
Integer

For Each i In
Application.Spreadsheets

SpreadsheetCount = SpreadsheetCount + 1

Next i

'if there is at least one spreadsheet

If SpreadsheetCount > 0 Then

'look for the data set (that wasn't active)

Set s = Application.ActiveDataSet

Call SpreadsheetResize(s,CaseNumber,VarNumber)

Else

'there are zero spreadsheets open

MsgBox "No spreadsheets are currently open"

End If

End Sub