Remove Blank Cells within each row

[David]

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]

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

[David]

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]

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