Using auto filtered data in Formulas

[alfredosos]

Dear All,


Some help on this would be greatly appreciated.


I have a column of text data (Column B). I would like this text data to be rolled up in to a single cell separated by commas and for it to be sensitive to filtered results.


E.g. If column A has been filtered to roll up the text that has been the product of that filter.


I have a solution for rolling the text up, but im having trouble with using the filtered results to roll up the text


Thanks in advance

 

[sergioMabres]

To work with a filtered range you could use

<code>
Dim rgAreas As Range: Set rgAreas = FilteredRg.SpecialCells(xlCellTypeVisible) 
Dim rgArea  As Range 
Dim rgCell  As Range  
For Each rgArea In rgAreas.Areas     
      For Each rgCell In rgArea.Cells         
           ' work with rgCell.Value      
      Next rgCell 
Next rgArea
</code>

I hope it hepls
Sergio

 

[alfredosos]

Thanks for your suggestion Sergio

Am afraid code is a new thing to me.

Is it easy enough to explain how to apply this?

I have been doing some research on how to add code, but am not sure where to add it nor how to apply this.

So do I just add in the code to a module, or just to the specified sheet that I am working with?

Im also not sure how this may work. how do I use this?
Do i need to specify which column I would like to retrieve filtered data for? and or which cell i want to retrieve this data to etc.

Or are there any more simple formula based ways. e.g. =SUbtotal works for numbers
Is there anything like that which will just retrieve the filtered results into a normal cell format, perhaps on a now table so that they can be used in the cell format?

Many thanks,
Alfredosos

 

[sergioMabres]

The code is a sample on how to use filtered rages, it should be copied to your already existing macro (sub) and modifed to to be rolled up in to a single cell using filtered results, you stated that you have a solution: "I have a solution for rolling the text up, but im having trouble with using the filtered results to roll up the text" I thought you already have a macro (sub) to do that.
If you do noy have one just send me an example of what you have and I show you in a sample file how to roll up in to a single cell using filtered results.
Regards
Sergio

 

[sergioMabres]

I have created a very simple sheet to show you how to roll up a filtered range, here is the code:

Sub rollthemup()
    Dim rgAreas, FilteredRg As Range
    Dim rgArea  As Range
    Dim rgCell  As Range
    Dim myValue1 As String
    Dim myValue2 As String
    Dim myCol As Integer
    ' Create range
    Set FilteredRg = Range("B2:C11")
    ' Add filter
    FilteredRg.AutoFilter Field:=1, Criteria1:="one"
    ' Copy to rgAreas only visible rows
    Set rgAreas = FilteredRg.SpecialCells(xlCellTypeVisible)
    ' Rollup values
    myValue1 = "Rolled up "
    myValue2 = "Rolled up "
    myCol = 1
    For Each rgArea In rgAreas.Areas
          For Each rgCell In rgArea.Cells
            If myCol = 1 Then
               myValue1 = myValue1 + ", " + rgCell.Value
               myCol = 2
            Else
               myValue2 = myValue2 + ", " + rgCell.Value
               myCol = 1
            End If
          Next rgCell
    Next rgArea
    Range("B1").Formula = myValue1
    Range("C1").Formula = myValue2
End Sub

And from here you can download the workbook, to test go to Macros and Run the macro
https://dl.dropbox.com/u/23094164/Alfredo1.xlsm

I hope it helps
Sergio