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.