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" <ksp.21jyhm_1137120901.8339@excelforum-nospam.com> wrote in message
news:ksp.21jyhm_1137120901.8339@excelforum-nospam.com...
>
> 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
>