Forum Discussion
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.
2 Replies
- NikolinoDEPlatinum Contributor
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=gbThere are many ways that can lead to Rome ... as well as with Excel 🙂
Nikolino
I know I don't know anything (Socrates) - NikolinoDEPlatinum Contributor
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.