Macro that goes through a filtered list

[da_shee]

Hi

I'm trying to write a macro that goes through every value in a filtered list.

Specifically, I'd like the macro to select the first value in the filter. Perform the operation. Select the second value in the filter. Perform the operation. Select the third value in the filter. Perform the operation. Etc...

Does anyone know if this is possible?

Shee

 

[Domski]

Hi Dom

The dictionary object is apparently faster than using a collection. At least that is what Bob Bovey, Dennis Wallentin, Stepeh Bullen and John Green tell us in Pro Excel Development 2nd edition. Not sure if it really makes any difference in the grand scheme of things. Just thought I'd share it with you.

Also the dictionary supports the Exists property which is a real helper here when it comes down to collecting a list of unique values. Although the method you have suggested also has a useful way of excluding dupes.

I'm not suggesting that there is anything wrong with your suggestion, only explaining why I opted for the dictionary in the 1st place.

Thanks for the explanation Jon, good to know there's more than one way/better ways to do things.

I did think about getting that book but I don't think my skills are quite up to that level as yet.

Laters,

Dom

 

[Jon von der Heyden]

Buy it mate! There are plenty of subjects / chapters that you could learn from. You can always tackle some of the other stuff later. I love it!!

Also, if you don't already have it I recommend you invest in Excel 2007 VBA Programmers Reference.

 

[Domski]

To answer your question.

I have a list of 130 shops listed alphabetically over 3400 cells in Column A.

I have products in Column B. The amount of products associated with each shop varies so each shop has a different number of rows. (Between ten and twenty).

I then have sales in column C.

What I'm trying to do is add a new row after the last product of each shop. Column A for this row would correspond to the shop name. Column B would be named total. Column C would be the sum of all sales of the products of this shop.

Hence my need for the macro.

Thanks

I may be missing the point again but if you just want to insert a blank row after each change in column A this would do it:

Sub test()
Dim lastRow As Long, myRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For myRow = lastRow To 3 Step -1
    If Cells(myRow, 1) <> Cells(myRow - 1, 1) Then
        Rows(myRow).Insert
        
    End If
Next myRow
End Sub

Dom

 

[Domski]

Buy it mate! There are plenty of subjects / chapters that you could learn from. You can always tackle some of the other stuff later. I love it!!

Also, if you don't already have it I recommend you invest in Excel 2007 VBA Programmers Reference.

Is the latter one worth getting if I already have John Walkenbach's Excel 2003 Power Programming With VBA?

Dom

 

[da_shee]

Both codes have actually come in very handy & I have solved the problem.

Thanks for all the help!

shee

 

[Jon von der Heyden]

Is the latter one worth getting if I already have John Walkenbach's Excel 2003 Power Programming With VBA?

Dom

Absolutely. I have JWALKS book too. The other one is a reference and it's helpful when you are going thru the pro development 2nd edition. I suppose it depends if you can be bothered to fork out another 25 quid. I have found all 3 to be incredibly helpful, although I think JWALK is considerably more 'basic' by comparison.