none
Fill blank row with the row right above RRS feed

  • Question

  • Hello ,

    I have an excel file and need 2 macros to execute below functions:

    1. The excel file have 37,464 rows and there are more blank rows on the same sheet, all I want to fill the blank rows with the data on the row right above to it, 

    For example, if row # 4 is blank, it should filled with the data from row#3

    2. Need to remove the rows with table captions wherever it appears on the sheet (highlighted with yellow for reference)

    https://docs.google.com/viewerng/viewer?url=http://metaupload.com/67U?download_token%3Df8018bf92be8f98627520c9787887b635a5bace6ecc22a04f1be67a162c78427

    Any help would be highly appreciated

    John Miller

    Monday, May 27, 2019 4:40 PM

Answers

  • 1) Here is a macro to delete rows with a specific text. The macro will prompt you to enter the necessary information.

    Sub DeleteRows()
        Dim s As String
        Dim c As String
        Dim n As Long
        Dim r As Long
        Dim m As Long
        s = InputBox("Enter the text to search for (e.g. FILE NO. 28-12)")
        If s = "" Then Exit Sub
        c = InputBox("Enter the column to search in (e.g. L)")
        If c = "" Then Exit Sub
        n = Val(InputBox("Enter the number of rows to delete (e.g. 7)"))
        If n <= 0 Then Exit Sub
        Application.ScreenUpdating = False
        m = Range(c & Rows.Count).End(xlUp).Row
        For r = m To 1 Step -1
            If Range(c & r).Value = s Then
                Range(c & r).Resize(n).EntireRow.Delete
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub

    You will have to run the macro once for each text string you want to search for.

    2) Do you want to copy data from the row above if an entire row is blank, or do you want to fill all empty cells with data from the row above even if other cells in the same row are filled already?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, May 28, 2019 8:20 PM
  • Here you go:

    Sub FillEmptyRows()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For r = 2 To m + 1
            If Application.CountA(Rows(r)) = 0 Then
                Rows(r - 1).Copy Destination:=Rows(r)
            End If
        Next r
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 29, 2019 11:11 AM
  • Here is a macro:

    Sub InsertRows()
        Dim s As String
        Dim c As String
        Dim n As Long
        Dim r As Long
        Dim m As Long
        s = InputBox("Enter the text to search for (e.g. OTR)")
        If s = "" Then Exit Sub
        c = InputBox("Enter the column to search in (e.g. I)")
        If c = "" Then Exit Sub
        n = Val(InputBox(Prompt:="Enter the number of rows to insert (e.g. 1)", Default:=1))
        If n <= 0 Then Exit Sub
        Application.ScreenUpdating = False
        m = Range(c & Rows.Count).End(xlUp).Row
        For r = m To 1 Step -1
            If Range(c & r).Value = s Then
                Range(c & r).Offset(1).Resize(n).EntireRow.Insert
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, May 30, 2019 2:42 PM
  • Thanks you so much Hans, it works perfect
    Thursday, May 30, 2019 2:49 PM
  • Sub FillEmptyRows()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For r = 2 To m + 1
            If Application.CountA(Rows(r)) = 0 Then
                Rows(r - 1).Copy Destination:=Rows(r)
                Rows(r).Interior.Color = vbYellow
            End If
        Next r
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, May 30, 2019 6:47 PM

All replies

  • Here is one macro that does both:

    Sub CompleteData()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Range("A" & Rows.Count).End(xlUp).Row
        For r = m To 1 Step -1
            If Range("A" & r).Value = "COLUMN 1" Then
                Range("A" & r).Resize(3).EntireRow.Delete
            ElseIf Range("A" & r).Value = "" Then
                Range("A" & r - 1).EntireRow.Copy Destination:=Range("A" & r)
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, May 27, 2019 6:49 PM
  • Thanks Hans for checking,

    1. It works, however I need the blank rows to be copy and pasted with the data right above to it, as I requested if row # 4 is blank, it should filled with the data from the cells in row#3 not the cells in row #2 or row #1.

    This macro copy the cells from row #2 if the cells in the row #3 is blank

    2. Is this possible to have a message box to pop up where I can feed specified text in the box to find & delete the range of rows or any particular row. (In this case the text would be "FILE NO. 28-12" in cell "L1 and delete all 7 shaded rows,

    and cell "A52"  "COLUMN TOT" 

    I have attached the file on which the rows/row to be deleted are shaded with Yellow for internal reference.

    Filed attached in the below link

    https://docs.google.com/viewerng/viewer?url=http://metaupload.com/67X?download_token%3D53dbe34d94401c2303d518bbd2ced4a117c74cf94ec9a64da97ea4d15d8a9765

    Thanks & Regards

    John

    Tuesday, May 28, 2019 12:14 PM
  • 1) Here is a macro to delete rows with a specific text. The macro will prompt you to enter the necessary information.

    Sub DeleteRows()
        Dim s As String
        Dim c As String
        Dim n As Long
        Dim r As Long
        Dim m As Long
        s = InputBox("Enter the text to search for (e.g. FILE NO. 28-12)")
        If s = "" Then Exit Sub
        c = InputBox("Enter the column to search in (e.g. L)")
        If c = "" Then Exit Sub
        n = Val(InputBox("Enter the number of rows to delete (e.g. 7)"))
        If n <= 0 Then Exit Sub
        Application.ScreenUpdating = False
        m = Range(c & Rows.Count).End(xlUp).Row
        For r = m To 1 Step -1
            If Range(c & r).Value = s Then
                Range(c & r).Resize(n).EntireRow.Delete
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub

    You will have to run the macro once for each text string you want to search for.

    2) Do you want to copy data from the row above if an entire row is blank, or do you want to fill all empty cells with data from the row above even if other cells in the same row are filled already?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, May 28, 2019 8:20 PM
  • Hello Hans,

    Thanks a lot, he first macro works exactly what's my request

    for the second one,

    I need to copy the data from the row above if an entire row is blank

    For example, the new file in the link has blank row on row #4 which should be filled with the data only from row #3 whether the cells in row #3 has value or empty, 

    So ideally for row #4  "the cells "A:C  & F & L" will get blank as there is no data in the cells in row #3 and the data in  cells "D:E &  G:K" should copied from row #3 and paste in row# 4.

    Similarly, the blank rows # 8 & 9 should filled with the data from row # 7

    https://docs.google.com/viewerng/viewer?url=http://metaupload.com/67Y?download_token%3Df5d64e287746721b2715714d83e3591b6c723d3b2bbb7feee6314a8da95251ff


    Tuesday, May 28, 2019 10:31 PM
  • Here you go:

    Sub FillEmptyRows()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For r = 2 To m + 1
            If Application.CountA(Rows(r)) = 0 Then
                Rows(r - 1).Copy Destination:=Rows(r)
            End If
        Next r
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, May 29, 2019 11:11 AM
  • Hi Hans,

    One final help, need to insert a new blank row right below wherever the word OTR" appears on the sheet.

    In this case, the file in the below link has the word "OTR" appears in "Column I", and I need a message box  prompt and asking column name and the text on the sheet where a new blank rows should insert right below to it.

    https://docs.google.com/viewerng/viewer?url=http://metaupload.com/682?download_token%3D6c307c8b548f6afce57b70470739e800c8dfe91aff1b0c3578aefaa1e6ca9d37

    Thanks & Regards

    John

    Thursday, May 30, 2019 2:07 PM
  • Here is a macro:

    Sub InsertRows()
        Dim s As String
        Dim c As String
        Dim n As Long
        Dim r As Long
        Dim m As Long
        s = InputBox("Enter the text to search for (e.g. OTR)")
        If s = "" Then Exit Sub
        c = InputBox("Enter the column to search in (e.g. I)")
        If c = "" Then Exit Sub
        n = Val(InputBox(Prompt:="Enter the number of rows to insert (e.g. 1)", Default:=1))
        If n <= 0 Then Exit Sub
        Application.ScreenUpdating = False
        m = Range(c & Rows.Count).End(xlUp).Row
        For r = m To 1 Step -1
            If Range(c & r).Value = s Then
                Range(c & r).Offset(1).Resize(n).EntireRow.Insert
            End If
        Next r
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, May 30, 2019 2:42 PM
  • Thanks you so much Hans, it works perfect
    Thursday, May 30, 2019 2:49 PM
  • Hans,

    Could you highlight the newly inserted row with yellow color

    Thursday, May 30, 2019 4:17 PM
  • Hans,

    Ignore the above request on highlight the newly inserted row with yellow color; instead

    Is that possible to highlight the data with yellow color after filled the Empty Rows by modifying the below macro

    Sub FillEmptyRows()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For r = 2 To m + 1
            If Application.CountA(Rows(r)) = 0 Then
                Rows(r - 1).Copy Destination:=Rows(r)
            End If
        Next r
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub


    Thursday, May 30, 2019 4:52 PM
  • Sub FillEmptyRows()
        Dim r As Long
        Dim m As Long
        Application.ScreenUpdating = False
        m = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        For r = 2 To m + 1
            If Application.CountA(Rows(r)) = 0 Then
                Rows(r - 1).Copy Destination:=Rows(r)
                Rows(r).Interior.Color = vbYellow
            End If
        Next r
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, May 30, 2019 6:47 PM