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

 

[Skyybot]

Try this on a copy of your Workbook as unexpected results may occur

Sub DeleteAllEmptyRows()
  Dim wb As Workbook, sht As Worksheet, rng As Range, cellx As Range
  Set wb = ThisWorkbook
  Set sht = wb.Worksheets("Staffing-Processes")
  Set rng = sht.Range("E2:E5000")
  For Each cellx In rng
    If IsEmpty(cellx) Then
        sht.Range(Cells(cellx.Row, 1), Cells(cellx.Row, 27)).ClearContents
    End If
Next cellx
End Sub

 

[Blanchetdb]

Try this on a copy of your Workbook as unexpected results may occur

Sub DeleteAllEmptyRows()
  Dim wb As Workbook, sht As Worksheet, rng As Range, cellx As Range
  Set wb = ThisWorkbook
  Set sht = wb.Worksheets("Staffing-Processes")
  Set rng = sht.Range("E2:E5000")
  For Each cellx In rng
    If IsEmpty(cellx) Then
        sht.Range(Cells(cellx.Row, 1), Cells(cellx.Row, 27)).ClearContents
    End If
Next cellx
End Sub

thank you ....

that seemed to have partially worked as the formula did not disappear and the unwanted info disappeared but the now, I have huge gaps in my data. What can be added to the code so that rows s move up to the next row where there is info in cell E. It is important that I do not have a gaps because the client selects options (for Editing) from a ComboBox within a UserForm to select options and having a gap will require them to do substantial scrolling.

 

[Skyybot]

I'm not sure what version of Excel you're using as your profile is not complete, so this may not work for you

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")
  For i = sht.UsedRange.Rows.Count To 2 Step -1
    If IsEmpty(sht.Cells(i, 5)) Then
        sht.Range(Cells(i, 1), Cells(i, 27)).Delete (xlUp)
    End If
Next i
End Sub

 

[Blanchetdb]

I'm not sure what version of Excel you're using as your profile is not complete, so this may not work for you

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")
  For i = sht.UsedRange.Rows.Count To 2 Step -1
    If IsEmpty(sht.Cells(i, 5)) Then
        sht.Range(Cells(i, 1), Cells(i, 27)).Delete (xlUp)
    End If
Next i
End Sub

I am using 2016

It didn't work ... the data didn't move up and column AB populated with #REF - Invalid cell reference error.

 

[Skyybot]

Try this

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")
  For i = sht.UsedRange.Rows.Count To 2 Step -1
    If IsEmpty(sht.Cells(i, 5)) Then
        sht.Range(Cells(i, 1), Cells(i, 27)).Delete (xlShiftUp)
    End If
Next i
End Sub

 

[Blanchetdb]

Try this

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")
  For i = sht.UsedRange.Rows.Count To 2 Step -1
    If IsEmpty(sht.Cells(i, 5)) Then
        sht.Range(Cells(i, 1), Cells(i, 27)).Delete (xlShiftUp)
    End If
Next i
End Sub

the info moved up but column AB still has the #REF - Invalid cell reference error.

the formula in the cell is: =#REF!&" / ("&#REF!&") / "&#REF!&" / "&#REF!

 

[Skyybot]

What is the Formula in the cell that is giving the REF error?

 

[Blanchetdb]

What is the Formula in the cell that is giving the REF error?

the formula starts at AB2 and continues down column AB

the formula is: =F2&" / ("&A2&") / "&AA2&" / "&X2

so this is what seems to be happening. I entered a new submission via the UserForm and it populated row 464 and the formula worked in row 464 but when I apply the code you provided, the info inn column 1 to 27moved up but the formula in 464 stayed there even though the data is now on row 415 as that was next available row.

if I change 27 to 28 .... the #REF does not appear but then the formula no longer appears after the last line which is problematic because it needs to remain throughout column AB for future entries

 

[Skyybot]

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