• 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.

Autofilter help

S

sesi96

New Member
I have several rows which I am currently assigning to 4 groups, a,b,c or d. As some of the rows could be in one or several of the groups, I am using 4 columns to filter on each group, i.e. a 1 in each row that is in that group and then use autofilter on the 4 sort columns to select the rows I want. Is it possible to consolidate this into one column and select either a,b,c or d and it filter for the rows. Hope this makes sense..
 
If I understand you correctly, you can make 1 column with a,b,c,d or thing like ab if it is in more than one group. If you then want to filter on group a, select 'Custom' from the drop down list (some where at the top) and choose contains in the top left box and type the group letter in the top right box


Hope this helps.
 
Another option, using VBA would be the following:


Sub Loop_Hide_Row()


'MACRO TO FILTER BASED ON COLUMN CONTENTS (BY HIDING ROWS NOT MEETING CRITERIA)

'SELECT THE HEADING CELL IN THE COLUMN AND THEN RUN MACRO


'DEFINE VARIABLE (COLUMN POSITION)

Dim offcol As Integer

offcol = ActiveCell.Column - 1


ActiveCell.Offset(1, 0).Activate


'LOOP TO FIND A 1. IF NO 1 EXISTS THEN HIDE THE ROW AND MOVE DOWN ONE

Do While ActiveCell.Offset(0, -offcol).Value <> ""


If ActiveCell.Value <> "1" Then


Selection.EntireRow.Hidden = True

ActiveCell.Offset(1, 0).Activate


'IF A 1 EXISTS THEN MOVE DOWN ONE ROW

Else: ActiveCell.Offset(1, 0).Activate


End If


Loop


End Sub


To view all rows you would then need to unhide the hidden rows.


NB If you are going to use this code please test it on a copy of your main file to ensure that nothing horrible happens!!
 
You must log in or register to reply here.
Back
Top