Filtering rows based on content from multiple columns

Question

I am stuck writing a macro that filters a list. I am getting some results, but not the ones i need.

Here is some speudocode that describes what i want.

Sub Filter1()
____Dim listOfRows As VBA.Collection
____Dim markForRemoval As VBA.Collection
____Dim row, column As Range
____Dim cell As Range
____
____Set listOfRows = New VBA.Collection
____Set markForRemoval = New VBA.Collection
____
____For Each row In F_RangeOfInterest()
________listOfRows.Add (row)
____Next

____For Each column In ActiveSheet.UsedRange.Columns
________If column.column > 2 Then
____________For Each row In listOfRows
____________Set cell F_GetCellFromIntersection( row, column )
________If true = F_CellHasContent(cell)
____________markForRemoval.Add (row)
________End If
________Next
________listOfRows = F_RemoveMarkes(listOfRows, markForRemoval)
____End If
____Next

____Application.ScreenUpdating = False
____For Each row In listOfRows
________row.hidden = true;
____Next
____Application.ScreenUpdating = True
End Sub

I failed implementing something liek this in VBA. Can you help me getting this to execute?

Edit #1: There was some confusion to what i try to achieve, so i enhanced the pseudocode.

Edit #2: I can see that the example code looks broken because of spacing. I added some characters to make spacing look better.

Also here is a screen to illustrate the scenario. The yellow lines are the ones that i want to filter using the macro above. Screen to illustrate problem

I don't quite understand what you're attempting but from your title...have you tried an Advanced Filter instead of VBA? — markblandford, May 17 '13 at 10:45
No i haven't. Using a macro is part of the userstory. I basically have a sheet with a couple of header rows. Below the headers the data starts. On the left column is a list of keys. All rows with keys that have no data have to be hidden. I can use a filter if i apply it via a macro. — Johannes, May 17 '13 at 10:49
I still dont get it. Show a screenshot of what your sheet looks like and what you want your output to look like — , May 17 '13 at 10:54
@Johannes so which line gives you error, and whats the error msg — , May 17 '13 at 11:19
@mehow this is pseudocode - so pretty much everything will produce an error. I don't know VBA enough to really fix this. I could easily program this a C based language or java - but VBA is just not for me. — Johannes, May 17 '13 at 11:45
@Johannes seriously, if you want us to help you have to be more specific. explain in detail what you what — , May 17 '13 at 11:56
@mehow the speudocode shows the outline of an algorithm that i want to use. I do not know the language specifics to make it work. I do not know how to use the lists exactly. Excel does not provide any errors and i do not have documentation foor VBA. I would love to be more specific - but the algorithm itself should be explanation enough - i can not figure out what information would be missing. — Johannes, May 17 '13 at 11:59
@Johannes so you want to hide the rows that are empty? you could do this with Filter or SpecialCells, **from a VBA Macro** see [StackOverflow: How to delete multiple rows without a loop in Excel VBA](http://stackoverflow.com/questions/15431801/how-to-delete-multiple-rows-without-a-loop-in-excel-vba) — Our Man in Bananas, May 17 '13 at 13:17
@Philip i will take a deeper look at that post - thanks. Using a filter seems to require a known ammount of columns. I do not know on how many columns this macro has to work. — Johannes, May 17 '13 at 14:28

score 0 · Accepted Answer · answered May 21 '13 at 13:41

This is the solution for my problem:

Sub Filter()
____Dim rangeOfInterest, cell, row As range
____Dim hidden() As Boolean
____Dim I, IMAX, OFFSET As Integer
____
____Set rangeOfInterest = F_GetRangeOfInterest()
____IMAX = rangeOfInterest.Rows.Count
____OFFSET = rangeOfInterest.row - 1
____
____ReDim hidden(IMAX)
____For I = 1 To IMAX
________hidden(I) = True
____Next I

____For Each cell In rangeOfInterest
________If cell.FormulaR1C1 <> "" Then
____________hidden(cell.row - OFFSET) = False
________End If
____Next

____Application.ScreenUpdating = False
____For Each row In rangeOfInterest.Rows
________row.hidden = hidden(row.row - OFFSET)
____Next
____Application.ScreenUpdating = True

End Sub

The algorithm is a little bit wasteful but I was unable to realize the idea stated in the question. The Collection behaves as if it converts anything to a Variant type - I just could not get that to run cerrectly. The current version iterates through cells that clearly no longer need to be checked since a cell on the left of the iterated cell has content. I did not fix that since my users are happy the way it is now.

Filtering rows based on content from multiple columns

1 Answers1