• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatically sorting to remove blank lines

MacGyver107

MacGyver107

New Member
Context:
I'm developing a dashboard info model and have a data entry table (A) of information (text and $values) that automatically pulls relevant line items to a mirrored table (B) based on user selection criteria - doing so leaves a number of lines blank in the mirrored table (B) where those line items do not meet the selection criteria.

Issue:
I now want to present the information in the mirrored table (B) as an output in a dashboard table (C), but don't want the blank lines to appear.

Suggestions?? (I have a sneaky feeling that this won't be my last query ;-p )
 
Hi MacGyver107,

Welcome to the forum..

If I am understanding your query correctly, you want to basically filter Col.B for blanks and delete those, right?

If that is the case, this can be done is various ways:

Here is the VBA approach:
Code: 
Sub Removing_blanks()
 
Cells.Select
Selection.AutoFilter
drc = Range("A" & Application.Rows.Count).End(xlUp).Row
ActiveSheet.Range("$B$1:$B$" & drc).AutoFilter Field:=2, Criteria1:="="
Rows("2:" & drc).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
ActiveSheet.ShowAllData
ActiveSheet.AutoFilterMode = False
MsgBox "Done"
 
End Sub

Manually Approach (1):
1. Select all cells (you can do that by clicking on the small box besides Col.A-Header)
2. Apply filter
3. Select blanks from B
4. Select Special by hitting alt +; keys
5. Use ctrl + (-) to delete the rows
6. Remove the Filter

Manually Approach (2):
1. Select Col.B
2. Goto Find & Select option on ribbon to the extreme right in the home tab
3. Select the blanks option box
4. Use ctrl + (-) to delete the rows

Hope this helps....:)
 
Hi Abhi,

Thank you for your response - I should have added some further context - the table data will change depending upon the selection criteria of the user, so I'm looking for a formula that will work automatically in the background (i.e. can't be manual...and I can't use VB ;-p ). I've been toying with a RANK formula coupled with a VLOOKUP but not quite getting it

Thanks for being the first response to my first post!
 
Apologies for asking this to you again, do you want to have a vlookup to pull the values from the index table or do you want to delete the blanks?
 
Xiq said:
How about a PivotTable and slicers?
Click to expand...
I wasn't aware that a pivot table would automatically update as the user changed search criteria so I hadn't looked into this...and I'm not familiar with slicers - is this a possibility?
 
Hi Mac and Gyver :p

I've uploaded file with a PivotTable + slicers. Please tell me what you think of this :)
 

Attachments

  • Example-MacGyver107.xlsx
    19.4 KB · Views: 4
You must log in or register to reply here.
Back
Top