Deleting Blank Rows then adding a row before a column value

Question

I'm new here and have been racking my brain for about a week to try and figure this out. I have also searched everywhere and every example I try to fix my issue doesn't work. So, I apologize if this is somewhere on here but couldn't find it.

My delema, I have a computer generated spreadsheet that creates over 2000 lines in Excel. Half are empty blank rows. My goal is to delete all of the empty rows then insert a rows before, if column "n" has a value of Y or N.

I have the delete the blank rows figured out but am struggling on adding the rows based on the value. Any help would be appreciated.

Sub DeleteBlankRows()
  Dim Rng As Range
  Dim WorkRng As Range

  On Error Resume Next
  xTitleId = "KutoolsforExcel"

  Set WorkRng = Application.Selection
  Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

  xRows = WorkRng.Rows.Count
  Application.ScreenUpdating = False

  For i = xRows To 1 Step -1
    If Application.WorksheetFunction.CountA(WorkRng.Rows(i)) = 0 Then
      WorkRng.Rows(i).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
    End If
  Next

  Application.ScreenUpdating = True
End Sub

Sub InsertRow()
  If ActiveCell = "y" Then
    Selection.EntireRow.Insert
    If ActiveCell = "n" Then
      Selection.EntireRow.Insert
    End If
  End If
End Sub

1st thing: In you `Sub InsertRow()` you put `If ActiveCell = "n" Then` inside y. — Makah, Dec 11 '13 at 21:31
You forgot to add `on error goto 0` at the end of your code too — Makah, Dec 11 '13 at 21:33

score 0 · Answer 1 · edited May 23 '17 at 11:49

Combine the logic of your first routine with the second (code not tested)

Sub DeleteBlankRows()

  Const COLUMN_TO_CHECK_FOR_Y_N = 10&

  Dim Rng As Range
  Dim WorkRng As Range

  On Error Resume Next
  xTitleId = "KutoolsforExcel"

  Set WorkRng = Application.Selection
  Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

  xRows = WorkRng.Rows.Count
  Application.ScreenUpdating = False

  ' Delete Rows
  For i = xRows To 1 Step -1
    If Application.WorksheetFunction.CountA(WorkRng.Rows(i)) = 0 Then
      WorkRng.Rows(i).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
    End If
  Next

  ' Add Rows
  For i = xRows To 1 Step -1
    If WorkRng.Cells(i, COLUMN_TO_CHECK_FOR_Y_N) = "N" Then
      WorkRng.Rows(i).Insert
    End If
  Next

  Application.ScreenUpdating = True
End Sub

Note that you should stay away from Selection and ActiveCell. See this answer.

You will probably need to update the constant COLUMN_TO_CHECK_FOR_Y_N from 10 to whatever column number your Y/N column is in. So, if your Y/N column is in column C, then the column number is 3. As always, step through the code if something doesn't work quite right. Good Luck! — Mike, Dec 11 '13 at 22:38

Deleting Blank Rows then adding a row before a column value

1 Answers1