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
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
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
