[AccessD] WAY OT:Excel Q

Darren,

'assuming your reports contain tables that have header columns but
otherwise lack values,
'you may want one of the following xxxDeleteBlankData subs.

Option Explicit
Public Sub ActiveCellCurrentRegionDeleteBlankData()
  RangeDeleteBlankData Excel.ActiveCell.CurrentRegion
End Sub
Public Sub SheetDeleteBlankData(Optional s As Excel.Worksheet)
  If s Is Nothing Then Set s = Excel.ActiveSheet
  RangeDeleteBlankData s.Range(1, 1).CurrentRegion
End Sub

Public Sub RangeDeleteBlankData(Optional r As Excel.Range)
  If r Is Nothing Then Set r = ActiveCell.CurrentRegion
  Dim col As Excel.Range
  For Each col In r.Columns
    If AllAreSpecialCells(col.Offset(1), xlCellTypeBlanks) Then
col.Delete (xlShiftToLeft)
  Next
End Sub

Public Function AllAreSpecialCells(r As Excel.Range, CellType As
XlCellType) As Boolean
  'Returns TRUE only if all the cells in the range are 'SpecialCells' of
type CellType
  On Error Resume Next ' error generated if NONE are special.  So,
return FALSE
  AllAreSpecialCells = r.Count = r.SpecialCells(CellType).Count
End Function

Malcolm Cook
Database Applications Manager, Bioinformatics
Stowers Institute for Medical Research 

>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com 
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darren DICK
>Sent: Tuesday, April 04, 2006 12:13 AM
>To: 'Access Developers discussion and problem solving'
>Subject: [AccessD] WAY OT:Excel Q
>
>Hi all
>Cross Posted to dbqSQL list
>I am outputting reports to Excel from reporting services (SQL)
>The reports have many tables on them with differing column widths
>When I output the reports to Excel I find many columns with no 
>data in them
> 
>Is there a way I can write some VBA and have it determine the 
>last row on the
>sheet with a value in it
>determine if there is any data in any cell from the top to the 
>bottom - if there
>is no data in the column
>delete it. Then 'tighten' all the columns?
> 
>E.G. Column A has data, column B not data, column c has data
>Delete column b and move column C to the left
> 
>E.G. AirCode
>For my sheet
>    find last.row with data ' that becomes the base point
>    find last.column with data ' that becomes a base point
>loop through columns & rows
>    for A to last column
>        from 1 to last row
>    if data = null then
>    delete column currentcolumn
>blah blah blah
> 
>Many thanks in advance
> 
>Darren
>-- 
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com
>