Deleting rows in Excel

Resolved · Low Priority · Version 2016

Thu 24 May 2018, 15:01 Delegate Ian said...

Ian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

Deleting rows in Excel

I want some code to look at three cells in the row below. If the active cell and the cell 7 columns to the left are blank then delete that row.
If the cell is column -8 is also blank then stop the loop (as we have reached the end of the data)
Error message of having a loop without a do, but I appear to have a do. Thank you for your help
PS I am a novice coder

Sub clearblanklines()
Do Until ActiveCell.Offset(1, 0).Value = ("") And ActiveCell.Offset(1, -7).Value = ("") And ActiveCell.Offset(1, -8).Value = ("")
If ActiveCell.Value = ("") And ActiveCell.Offset(0, -7).Value = ("") Then
Rows(ActiveCell.Offset(1, 0).Row).EntireRow.Delete

Else
ActiveCell.Offset(1, 0).Select
Loop

End Sub

Thu 24 May 2018, 16:43 Delegate Ian said...

RE: deleting rows in Excel

I have a solution that delivers what I need. Please see below. I still don't understand what I am doing wrong with the code in my original question

Sub clearblanklines2()
' this is to clear out the blank lines
Dim count As Integer
For count = 1 To 300

If ActiveCell.Offset(1, 0).Value = ("") And ActiveCell.Offset(1, -7).Value = ("") Then
Rows(ActiveCell.Offset(1, 0).Row).EntireRow.Delete

Else
ActiveCell.Offset(1, 0).Select
End If
Next count
End Sub

Thu 24 May 2018, 20:23 Trainer Jens said...

RE: deleting rows in Excel

Hi Ian,

Thank you for the forum question.

The error message of having a loop without a do is because you do not have an End If.

It is not a very helpful error message as you have found out.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector