Forum Discussion

WDave1975's avatar
WDave1975
Copper Contributor
Sep 26, 2020

Removing blank lines

I've got a master sheet with data then I'm filtering a column and pulling that information into separate tabs, based on criteria.  The master sheet is roughly 1,400 lines long.  How do I remove blank lines in the tabs?  For example, lines 1, 6, 34, and 45 are pulled into tab A, but on tab A, I have blank lines between lines 1, 6, 34, and 45.  I basically want to filter out blanks automatically when the data is pulled from the master sheet into the designated tabs.

Formulas and Functions

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 26, 2020

    WDave1975 

     

    An additional suggested solution would be to remove or hide the empty lines with a autofilter oder IF Formula,


    here a samble with formula :

    {=IF(SUMPRODUCT(($A$1:$A$100<>"")*1<ROW(A1),"",INDEX(A:A,SMALL(IF($A$1:$A$100<>"",ROW($1:$100)),ROW(A1))))}
    *Contains array formula:

    Do not enter the border {},
    complete the formula with CTRL + SHIFT + RETURN!

     

    Use AutoFilter to filter your data
    https://support.microsoft.com/en-gb/office/use-autofilter-to-filter-your-data-7d87d63e-ebd0-424b-8106-e2ab61133d92?ui=en-us&rs=en-gb&ad=gb

     

    There are many ways that can lead to Rome ... as well as with Excel 🙂

     

    Nikolino
    I know I don't know anything (Socrates)

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 26, 2020

    WDave1975 

     

    Untested, you need to adjust the area / lines. Should actually work without a problem

     

    Sub button1_click ()
Dim i As Long

'Customize sheet name
With Worksheets ("Sheet1")
     For i = 140 To 5 Step -1
         If WorksheetFunction.CountBlank (.Range ("J" & i & ": R" & i)) = 9 Then
             .Rows (i) .Delete
         End If
     Next i
End With
End Sub

     

     

    if you don't want to delete the lines but just hide them?

     

    .Rows(i).Hidden = True

     

     

    I would be happy to know if I could help.

     

    Nikolino

    I know I don't know anything (Socrates)

     

    * Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Resources