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).EntireRow) = 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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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).EntireRow) = 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
>

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