Run time error

[Richard]

I'm having this problem when trying to delete empty cells
using this method but I'm getting a run time error
1004 "no cells found". I'd like for all the empty cells to
be deleted after each worksheet_change not in the
before_close but I can't get this code to work at all
there. Any sugestions. Please!! Thanks in advance!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo inputagain
[A:A].SpecialCells(xlBlanks).EntireRow.Delete
inputagain: Exit Sub
End Sub

 

[Norman Jones]

Hi Richard,

You may be hitting the SpecialCells 8192 contiguous areas problem.

See:

Microsoft Knowledge Base Article - 832293
The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel

http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

 

[Richard]

Where is the Article located

-----Original Message-----
Hi Richard,

You may be hitting the SpecialCells 8192 contiguous areas problem.

See:

Microsoft Knowledge Base Article - 832293
The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel

http://support.microsoft.com/default.aspx?scid=kb;en- us;832293


---
Regards,
Norman

I'm having this problem when trying to delete empty cells
using this method but I'm getting a run time error
1004 "no cells found". I'd like for all the empty cells to
be deleted after each worksheet_change not in the
before_close but I can't get this code to work at all
there. Any sugestions. Please!! Thanks in advance!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo inputagain
[A:A].SpecialCells(xlBlanks).EntireRow.Delete
inputagain: Exit Sub
End Sub

.

 

[Norman Jones]

Hi Richard,

Try this link:

The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel

or

http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

which both worked for me.


---
Regards,
Norman

Where is the Article located

-----Original Message-----
Hi Richard,

You may be hitting the SpecialCells 8192 contiguous areas problem.

See:

Microsoft Knowledge Base Article - 832293
The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel

http://support.microsoft.com/default.aspx?scid=kb;en- us;832293


---
Regards,
Norman

I'm having this problem when trying to delete empty cells
using this method but I'm getting a run time error
1004 "no cells found". I'd like for all the empty cells to
be deleted after each worksheet_change not in the
before_close but I can't get this code to work at all
there. Any sugestions. Please!! Thanks in advance!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo inputagain
[A:A].SpecialCells(xlBlanks).EntireRow.Delete
inputagain: Exit Sub
End Sub

.

 

[Norman Jones]

Hi Richard,

For reasons beyond my immediate knowledge, the first link has not
trancribed.

The second link, however, seems fine.

If it fails for you, do a search for the Knowledge Base article 832293 on th
MS site.

---
Regards,
Norman

Hi Richard,

Try this link:

The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel

or

http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

which both worked for me.


---
Regards,
Norman

Where is the Article located

-----Original Message-----
Hi Richard,

You may be hitting the SpecialCells 8192 contiguous areas problem.

See:

Microsoft Knowledge Base Article - 832293
The .SpecialCells(xlCellTypeBlanks) VBA function does not work as expected
in Excel

http://support.microsoft.com/default.aspx?scid=kb;en- us;832293


---
Regards,
Norman



I'm having this problem when trying to delete empty cells
using this method but I'm getting a run time error
1004 "no cells found". I'd like for all the empty cells to
be deleted after each worksheet_change not in the
before_close but I can't get this code to work at all
there. Any sugestions. Please!! Thanks in advance!
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error GoTo inputagain
[A:A].SpecialCells(xlBlanks).EntireRow.Delete
inputagain: Exit Sub
End Sub


.