Solved: Find & clear blank rows

[dgt]

Hi all

My workbook has suddenly slowed even more than usual and I suspect from previous experience that something has happened to a particular worksheet; a cut-down sample of which is enclosed in the attachment.

The sample worksheet contains no formulas or VBA as per the original.

The row after each clients set of data, which occupy varying number of rows, should be totally blank.

e.g. Rows 8, 14, 22, 25, 33, 38, 41, 46, 51 in the attachment.

I am looking for a macro that will check each of these rows and delete any value or text within the row but not actually delete the row itself.

Bear in mind that there are huundreds more rows and columns not shown in the example but all of the same format.

Hope somebody can help with this ...David

[Bob Phillips]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<<<< change to suit
Dim Lastrow As Long
Dim i As Long
Dim cell As Range

Application.ScreenUpdating = False

With ActiveSheet

Lastrow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 5 To Lastrow

If Cells(i, TEST_COLUMN).Value2 = "" Then

.Rows(i).ClearContents
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[dgt]

Hi xld

Thanks for that very quick response.

Just checking, that I use this code in a standard module because you have declared it as a "Public Sub" which I have never used before.

David

[kroz]

public sub = sub - standard macro header
You can put it in any Module and run it with ALT + F8

[Bob Phillips]

Hi xld

Thanks for that very quick response.

Just checking, that I use this code in a standard module because you have declared it as a "Public Sub" which I have never used before.

David

Yes, Public is the default, I just like to be explicit.

[dgt]

Thanks for all your help again, it did'nt cure the problem but I'm going to keep this as a maintenance macro for future use.

In the end I had to revert to using last weeks workbook and add-in all of the data and changes for this week. So far, no problems!

David