This seemed to work ok--but test it just in case.


Option Explicit
Sub testme01()

Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no blanks in column A"
Exit Sub
End If

Intersect(myRng.EntireRow, .Columns("A:P")) _
.Cells.SpecialCells(xlCellTypeBlanks).Delete shift:=xlToLeft
End With
End Sub



David wrote:
>
> Dave,
> Thank you works very well. Is it possible to put an IF statement in there?
>
> Like If cell in column A is blank,
> then execute the code,
> else goto next row?
>
> "Dave Peterson" wrote:
>
> > You can do this manually:
> >
> > Select A:M
> > Edit|goto|special|blanks
> > Edit|delete|shift left
> >
> > In code, it would look like:
> >
> > On Error Resume Next
> > ActiveSheet.Columns("A:P").Cells.SpecialCells(xlCellTypeBlanks).Delete _
> > shift:=xlToLeft
> > On Error GoTo 0
> >
> > I don't know anything about Access.
> >
> > And is starting in column A ok?
> >
> > David wrote:
> > >
> > > Hello all,
> > > I am exporting data from Access 2002 to Excel. Unfortunately, I am
> > > exporting a sub report that produces blank cells in columns A thru M for the
> > > sub report data. For example my ONE relationship of Customer is on row 1.
> > > The many relationship ORDERS is on rows 2 thru 5, but the data on rows 2 thru
> > > 5 doesn't start until column N.
> > > I have put this bit of code together that seems to work for Row 1.
> > >
> > > How can I modify it to cycle thru all of the rows with data?
> > >
> > > If a1 = "" Then
> > > Range("b1:M1").Select
> > > Selection.Delete Shift:=xlToLeft
> > > Range("a1:a1").Select
> > > End If
> > >
> > > Is there also a way to pass this code/macro from Access to the new Excel doc
> > > the users are creating from Access? Or get this code to execute after Access
> > > has completed the export of data to the excel document?
> > >
> > > Any assistance you can provide is greatly appreciated.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson