Macro that goes through a filtered list

da_shee

New Member
Joined
Sep 16, 2009
Messages
37
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
 
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
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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.
 
Upvote 0
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:

Code:
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
 
Upvote 0
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
 
Upvote 0
Both codes have actually come in very handy & I have solved the problem.

Thanks for all the help!

shee
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top