Macro Help for Deleting Blank Rows & Clearing Cell Contents
 

I have several columns of data in a worksheet (A1:I200), that is hard
coded to another worksheet ie cell A1 = Sheet2!AB1, A2 = Sheet2!AB2 and
so on

Sometimes there is no data in the source cells which is resulting in
many blank rows in the destination worksheet.

I found the following macro that does delete blank rows, but the entire
row must be empty for it to work

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I am able to get rid of the formula’s by doing a paste value, however
the cells are obviously not truly empty as the macro is not working.
(If I manually select the cells and press the delete key the macro
works)

Does anyone know a way of a macro that I can use to clear the cell
contents of these cells, or any suggestions on how to get rid if these
blank unwanted rows?

Thanks
Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=500949

Macro Help for Deleting Blank Rows & Clearing Cell Contents
 

Dave Peterson suggest selecting the Range and doing

Edit=Replace
Replace What: <Leave Blank
Replace With" $$$$$

then do it again, but reversed

Replace What: $$$$$
Replace With: <leave blank

If you need code, turn on the macro recorder while you do it manually.

--
Regards,
Tom Ogilvy

"ksp" wrote in message
...

I have several columns of data in a worksheet (A1:I200), that is hard
coded to another worksheet ie cell A1 = Sheet2!AB1, A2 = Sheet2!AB2 and
so on

Sometimes there is no data in the source cells which is resulting in
many blank rows in the destination worksheet.

I found the following macro that does delete blank rows, but the entire
row must be empty for it to work

Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I am able to get rid of the formula's by doing a paste value, however
the cells are obviously not truly empty as the macro is not working.
(If I manually select the cells and press the delete key the macro
works)

Does anyone know a way of a macro that I can use to clear the cell
contents of these cells, or any suggestions on how to get rid if these
blank unwanted rows?

Thanks
Karen


--
ksp
------------------------------------------------------------------------
ksp's Profile:
http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=500949

Macro Help for Deleting Blank Rows & Clearing Cell Contents
 

Thanks Tom

Strangely enough I had just been playing with using the replace
function and had just gotten it to work

Thanks though for your input


--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=500949