I have a spread sheet which in column A I place a Y when I invoice the line. I wish at the end of the month to delete ALL Rows with a Y in column A only, then move all rows up so there are no blank lines in the spread sheet. How is the quickest way to do this as I have 26 separate spread sheets I need to do it in every end of month?


You need this VBA (Macro) to delete all rows which has Y in Column A, across Worksheets.

Option Explicit

Sub DeleteRows()

Dim ws As Worksheet
Dim lr As Long
Dim i As Long

Application.ScreenUpdating = False

For Each ws In Worksheets

lr = ws.Range("A" & Rows.Count).End(xlUp).Row

For i = lr To 1 Step -1

 If ws.Range("A" & i) = "Y" Then
    ws.Range("A" & i).EntireRow.Delete
 End If
 Next i
 Next ws

 Application.ScreenUpdating = True

 MsgBox "Rows Deleted"

End Sub


  • Copy & Paste this code as Standard module.
  • Search String "Y" to delete Rows across Sheets is editable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.