Delete Blank row from sheet3 onwards

[howard]

I have written code to delete that rows from row 2 onwards from sheet 3 where there ia nothing in COl A

when Running the macro I get run time error "For without Next"


It would be appreciated if someone could kindly amend my code

 Sub Delete_Blank_Row()
Dim lr As Long, I As Long, R As Long

lr = Cells(Rows.Count, "A").End(xlUp).Row
  For I = 3 To Worksheets.Count
 For R = lr To 2 Step -1
   
With Worksheets(I)

    If Range("A" & R).Value = "" Then
        Rows(R).Delete
    End If
    
End With
Next R

End Sub

 

[Joe4]

You are missing the "Next I" statement (you have 2 "For" statements, but only one "Next" statement).
(Sometimes the error messages are helpful and tell you exactly what is going on!)

 

[howard]

Thanks for the help


I have amended my code as below

Sub Delete_Blank_Row()
Dim lr As Long, I As Long, R As Long

lr = Cells(Rows.Count, "A").End(xlUp).Row
  For I = 3 To Worksheets.Count
 For R = lr To 2 Step -1
   
With Worksheets(I)

    If Range("A" & R).Value = "" Then
        Rows(R).Delete
    End If
    
End With
Next R
Next I

End Sub

However, If col A is blank from row 2 onwards, the row is now deleted



Please check my code and kindly amend



See Sample Data

Book1
	A	B	C	D	E	F
1	Division	Branch	Stock Number	Days	Period	Sheet
2				257	91-180+	Summary
3
4
5
6
Sheet1

 

[Joe4]

However, If col A is blank from row 2 onwards, the row is now deleted

Isn't that what you are asking for?

From your original post:

I have written code to delete that rows from row 2 onwards from sheet 3 where there ia nothing in COl A

You might need to be more detailed in your explanation.

 

[howard]

Hi Joe


If for eg A2 is blank, then row 2 to be deleted, if A5 is blank, then that row to be deleted etc

 

[Joe4]

For rows that have data in them, will one column always have data (like column D)?
I am trying to ascertain which column I can look at to determine where your data actually ends on each sheet.

It looks like you are trying to use column A, but that is a problem if column A is blank.
And, you are only doing it on the sheet that you start on, not on each sheet (the calculation needs to be within your loop).

 

[Joe4]

If column D is the column you can use to locate the last row, this code should do what you want. If not, just change "D" to the appropriate column reference:

Sub Delete_Blank_Row()

    Dim lr As Long, I As Long, R As Long

    Application.ScreenUpdating = False
    
    For I = 3 To Worksheets.Count
        lr = Sheets(I).Cells(Rows.Count, "D").End(xlUp).Row
        For R = lr To 2 Step -1
            If Sheets(I).Range("A" & R).Value = "" Then
                Sheets(I).Rows(R).Delete
            End If
        Next R
    Next I

    Application.ScreenUpdating = True

End Sub

 

[Fluff]

beaten 2it

 

[howard]

Thanks for your help and input Joe


I have used Col P to find the last row as that will always have data in

 

[Joe4]

Great. Then I believe you should be all set.