Delete rows based on blank cell; then copy formula to an entire column

[Blanchetdb]

Hi

I am desperately seeking an answer to this issue....

I have the code to erase the blank cells:

Sub DeleteAllEmptyRows()
  On Error Resume Next
    Sheets("Staffing-Processes").Range("E2:E5000").Select
    Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

First - I would like the code to delete rows in multiple worksheets (all worksheets; 10 of them) are all designed the same

Second - The more pressing issue is that as the rows are deleted but so is the formula located in column AB which is imperative that it remains. This is the formula in column AB: =F2&" / ("&A2&") / "&AA2&" / "&X2 which starts on AB2.

I need to be able to delete rows where the cell in column E is blank but also ensure that the formula located in column AB remains because it is needed for NEW entries at a later date

PLEASE any help would be greatly appreciated.

thank you.
Dan

 

[Fluff]

I am using 2016

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Blanchetdb]

Sub DeleteAllEmptyRows()
  Dim wb As Workbook, sht As Worksheet, rng As Range, cellx As Range, i As Long
  Set wb = ThisWorkbook
  Set sht = wb.Worksheets("Staffing-Processes")
  Set rng = sht.Range("E2:E5000")
  For i = rng.Rows.Count + 1 To 2 Step -1
    If IsEmpty(rng.Cells(i - 1)) Then
        'Debug.Print rng.Cells(i).Value
        sht.Rows(i).Delete
    End If
Next i
sht.Cells(2, 28).Copy
sht.Range(Cells(3, 28), Cells(5000, 28)).PasteSpecial (xlPasteFormulas)
End Sub

IT WORKED !!!! I really appreciate your help with this

 

[Skyybot]

Np. Happy to help @Blanchetdb