macro to delete empty cells
 

Sub RemoveEmptyCells_ShiftUp()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If isempty(cells(rw,1)) Then _
cells(rw,1).Delete Shift:=xlShiftUp
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

This deletes blank cells in Column A ( second argument of cells is 1) and
shifts the cell up.

--
Regards,
Tom Ogilvy


"Todd" wrote in message
...
Hi, I am playing with a marcro to delete empty cells. I
have a macro that deletes empty rows and I thought I could
basically change "rows" to "cells" and it would work. But
I guess its not that simple. Can someone tell me what I
need to make this work?

TIA

Todd.

Here is the origonal macro.

Sub RemoveEmptyRows()
Application.ScreenUpdating = False 'xlManual below in
Xl95
Application.Calculation = xlCalculationManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Row.Count To 1 Step -1
If Application.CountA(Rows(rw).Entirecell) = 0 Then _
Rows(rw).Delete
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True 'xlAutomatic above
in xl95
End Sub

macro to delete empty cells
 

Todd,

I have found using the Recorder with a dummy workbook is a great way to find
code for various situations. I use this all the time when building new code
(either because I don't know, or have forgotton).

Don't forget the Help function. (not always easy to find what you want, but
most of what you want is in there some where)

When that fails, I search Google. Go to this site and download the Google
Search Add-In (its a great help)
www.rondebruin.nl/Google.htm

And than I follow the ng. Lots of great stuff. And they constantly answer
my questions (no matter how dumb they may be).

steve

"Todd" wrote in message
...
Thanks so much,

How would I change this to get it to shift in different
directions? I have been looking on the net for a page of
basic commands so I can learn this but haven't found
anything yet.


Todd




-----Original Message-----
Sub RemoveEmptyCells_ShiftUp()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If isempty(cells(rw,1)) Then _
cells(rw,1).Delete Shift:=xlShiftUp
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

This deletes blank cells in Column A ( second argument
of cells is 1) and
shifts the cell up.

--
Regards,
Tom Ogilvy


"Todd" wrote in message
...
Hi, I am playing with a marcro to delete empty cells. I
have a macro that deletes empty rows and I thought I
could
basically change "rows" to "cells" and it would work.
But
I guess its not that simple. Can someone tell me what I
need to make this work?

TIA

Todd.

Here is the origonal macro.

Sub RemoveEmptyRows()
Application.ScreenUpdating = False 'xlManual below
in
Xl95
Application.Calculation = xlCalculationManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Row.Count To 1 Step -
1
If Application.CountA(Rows(rw).Entirecell) = 0
Then _
Rows(rw).Delete
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True 'xlAutomatic
above
in xl95
End Sub


.

macro to delete empty cells
 

It can only shift up or shift to the left.

so the argument would be xltoLeft

highlight the delete command in the module and hit F1 to see the help file
with the options.


--
Regards,
Tom Ogilvy

"Todd" wrote in message
...
Thanks so much,

How would I change this to get it to shift in different
directions? I have been looking on the net for a page of
basic commands so I can learn this but haven't found
anything yet.


Todd




-----Original Message-----
Sub RemoveEmptyCells_ShiftUp()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
If isempty(cells(rw,1)) Then _
cells(rw,1).Delete Shift:=xlShiftUp
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

This deletes blank cells in Column A ( second argument
of cells is 1) and
shifts the cell up.

--
Regards,
Tom Ogilvy


"Todd" wrote in message
...
Hi, I am playing with a marcro to delete empty cells. I
have a macro that deletes empty rows and I thought I
could
basically change "rows" to "cells" and it would work.
But
I guess its not that simple. Can someone tell me what I
need to make this work?

TIA

Todd.

Here is the origonal macro.

Sub RemoveEmptyRows()
Application.ScreenUpdating = False 'xlManual below
in
Xl95
Application.Calculation = xlCalculationManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Row.Count To 1 Step -
1
If Application.CountA(Rows(rw).Entirecell) = 0
Then _
Rows(rw).Delete
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True 'xlAutomatic
above
in xl95
End Sub


.

macro to delete empty cells
 

Thanks again, you both have been great

Todd
-----Original Message-----
It can only shift up or shift to the left.

so the argument would be xltoLeft

highlight the delete command in the module and hit F1 to
see the help file
with the options.


--
Regards,
Tom Ogilvy

"Todd" wrote in message
...
Thanks so much,

How would I change this to get it to shift in different
directions? I have been looking on the net for a page
of
basic commands so I can learn this but haven't found
anything yet.


Todd




-----Original Message-----
Sub RemoveEmptyCells_ShiftUp()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Rows.Count To 1
Step -1
If isempty(cells(rw,1)) Then _
cells(rw,1).Delete Shift:=xlShiftUp
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

This deletes blank cells in Column A ( second argument
of cells is 1) and
shifts the cell up.

--
Regards,
Tom Ogilvy


"Todd" wrote in message
...
Hi, I am playing with a marcro to delete empty
cells. I
have a macro that deletes empty rows and I thought I
could
basically change "rows" to "cells" and it would work.
But
I guess its not that simple. Can someone tell me
what I
need to make this work?

TIA

Todd.

Here is the origonal macro.

Sub RemoveEmptyRows()
Application.ScreenUpdating = False 'xlManual
below
in
Xl95
Application.Calculation = xlCalculationManual
Dim rw As Long, iCol As Long
For rw = ActiveSheet.UsedRange.Row.Count To 1
Step -
1
If Application.CountA(Rows(rw).Entirecell) = 0
Then _
Rows(rw).Delete
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True 'xlAutomatic
above
in xl95
End Sub


.



.